[Solved] How to send email for each customer in pivot table
- Thread starter bigme
- Start date
You can easily solve your problem by following the below steps:
General code:
How to Send Email If Conditions Met in Excel (3 Easy Methods)
Send Reminder Email Automatically from an Excel Worksheet Using VBA
How to Send Email Automatically When Condition Met in Excel
- At first, you have to convert your pivot table into a normal table simply by copying and pasting it into another worksheet. Next, you need to apply the VBA code to do your task. In the Recipents column you need to input your proper customer email address. For demonstration purpose, I use the following email address.
- To open the VBA window press ALT+ F11.
- Click on the Insert tab.
- From the Insert tab, select the Module option.
- To send emails automatically, paste the following VBA codes.
Code:
Public Sub SendMail()
'Declare the variables
Dim XDueDate As Range
Dim XRcptsEmail As Range
Dim xMailContent As Range
Dim xRngDn As Range
Dim xCrtOut As Object
Dim xValDateRng As String
Dim xValSendRng As String
Dim k As Long
Dim xMailSections As Object
Dim xFinalRw As Long
Dim CrVbLf As String
Dim xMsg As String
Dim xSubEmail As String
On Error Resume Next
'To select the date column insert a input box
Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", , , , , , 8)
If XDueDate Is Nothing Then Exit Sub
'Insert a input box for selecting the recipients
Set XRcptsEmail = Application.InputBox("Choose the column for the email addresses of the recipients:", "ExcelDemy", , , , , , 8)
If XRcptsEmail Is Nothing Then Exit Sub
'To enter the text mail, insert a input box
Set xMailContent = Application.InputBox("In your email, choose the column with the reminded text:", "ExcelDemy", , , , , , 8)
If xMailContent Is Nothing Then Exit Sub
'Count rows for the due dates
xFinalRw = XDueDate.Rows.Count
Set XDueDate = XDueDate(1)
Set XRcptsEmail = XRcptsEmail(1)
Set xMailContent = xMailContent(1)
'Set command to open MS Outlook Application
Set xCrtOut = CreateObject("Outlook.Application")
'Apply For loop to conduct the operation in each row one by one
For k = 1 To xFinalRw
xValDateRng = ""
xValDateRng = XDueDate.Offset(k - 1).Value
'Apply If condition for the Due Date values
If xValDateRng <> "" Then
'Condition set to send mail if the difference between due dates and current date is greater than 1 and less than 7 days
'Means 1 < X< 7, X = Due Date - Current Date
If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
xValSendRng = XRcptsEmail.Offset(k - 1).Value
'Create the subject, body and text contents with the required variables
xSubEmail = xMailContent.Offset(k - 1).Value & " on " & xValDateRng
CrVbLf = "<br><br>"
xMsg = "<HTML><BODY>"
xMsg = xMsg & "Dear " & xValSendRng & CrVbLf
xMsg = xMsg & "Text : " & xMailContent.Offset(k - 1).Value & CrVbLf
xMsg = xMsg & "</BODY></HTML>"
'Create the email
Set xMailSections = xCrtOut.CreateItem(0)
'Define the position to place the Subject, Body and Recipients Address
With xMailSections
.Subject = xSubEmail
.To = xValSendRng
.HTMLBody = xMsg
.Display
.Send
End With
Set xMailSections = Nothing
End If
End If
Next
Set xCrtOut = Nothing
End Sub- After saving, press F5 to run the program.
- In the Date input box, select the Deadline column with the due dates.
- Then, click OK.
- In the recipients’ email input box, select the Recipients column range of cells.
- Press Enter.
- Select the range in the final input box for the Text column.
- Finally, click OK.
- As a result, all of your emails will be sent to each customer with the data before the due dates.
How to Send Email If Conditions Met in Excel (3 Easy Methods)
Send Reminder Email Automatically from an Excel Worksheet Using VBA
How to Send Email Automatically When Condition Met in Excel
Last edited:
dear Mr. Saquib,
after i try and run your code, its not exactly what i'm looking for...
i'm convert my excel and still confuse how to send each current region (yellow highlight) as a email body and send it with list of email address in other sheets so it will create multiple email, could you help me please.... thank you.
after i try and run your code, its not exactly what i'm looking for...
i'm convert my excel and still confuse how to send each current region (yellow highlight) as a email body and send it with list of email address in other sheets so it will create multiple email, could you help me please.... thank you.
You can easily solve your problem by following the steps below:
You must apply the VBA code to do your task. In the Email address column, you need to input your proper customer email address. For demonstration purposes, I use the following email address.
You must apply the VBA code to do your task. In the Email address column, you need to input your proper customer email address. For demonstration purposes, I use the following email address.
- To open the VBA window press ALT+ F11.
- Click on the Insert tab.
- From the Insert tab, select the Module option.
- To send emails automatically, paste the following VBA codes.
Code:
Sub ExcelToOutlookSR()
Dim mApp As Object
Dim mMail As Object
Dim SendToMail As String
Dim MailBody1 As String
Dim MailBody2 As String
Dim MailBody3 As String
Dim MailBody4 As String
For Each r In Selection
SendToMail = Range("F" & r.Row)
MailBody1 = Range("B" & r.Row)
MailBody2 = Range("C" & r.Row)
MailBody3 = Range("D" & r.Row)
MailBody4 = Range("E" & r.Row)
Set mApp = CreateObject("Outlook.Application")
Set mMail = mApp.CreateItem(0)
With mMail
.To = SendToMail
.Body = "Customer Name: " & MailBody1 & ", Text: " & MailBody2 & ", Net Due Date: " & MailBody3 & " , Total: " & MailBody4
.Display ' You can use .Send
End With
Next r
End Sub- Next, select the customer name, and press F5 to run the program. [ The email body will be blank if the customer name wasn't selected]
- As a result, all of your emails will be sent to each customer with the data
