Today I'm going to explain you how to generate Outlook email and populate with the required details of a worksheet automatically using VBA.
Here below is a example worksheet. It contains ID1, Email Addess, First name and Data.
And there is a button call "E-mail" in that sheet. What we need to do is Once we click the button it should ask us to enter row number of data we want to email. When we input that number and press OK, Message should be opened with required data.
So first we need to get the row number from the user.
|
Dim RowNo As Integer
RowNo = InputBox("Please enter the row number you need to email:", "")
|
So it will show this input box.
Then we need to create the body of the email.
|
Dim WS As Worksheet Dim messageBody As String Set WS = ActiveSheet messageBody = "Dear " & WS.Range("C" & RowNo) & "," & vbCrLf & vbCrLf & _ "Your ID1: " & WS.Range("A" & RowNo) & vbCrLf & vbCrLf & _ "Your email address: " & WS.Range("B" & RowNo) & vbCrLf & vbCrLf & _ "Your data: " & WS.Range("D" & RowNo) & vbCrLf & vbCrLf & _ "Please store this information in a safe place." & vbCrLf & vbCrLf & _ "Kind regards," & vbCrLf & vbCrLf & _ "Paul" |
So message will be constructed similar to below.
Finally we need to create the outlook mail. Below code will do that part.
|
Dim OutApp As Outlook.Application
Dim objMsg As MailItem
Set OutApp = CreateObject("Outlook.Application")
Set objMsg = OutApp.CreateItem(olMailItem)
With objMsg
.Body = messageBody
.Display
End With
|
And here is the full code for this automation.
|
Dim RowNo As Integer
RowNo = InputBox("Please enter the row number you need to email:", "")
Dim WS As Worksheet
Dim messageBody As String
Set WS = ActiveSheet
messageBody = "Dear " & WS.Range("C" & RowNo) & "," & vbCrLf & vbCrLf & _
"Your ID1: " & WS.Range("A" & RowNo) & vbCrLf & vbCrLf & _
"Your email address: " & WS.Range("B" & RowNo) & vbCrLf & vbCrLf & _
"Your data: " & WS.Range("D" & RowNo) & vbCrLf & vbCrLf & _
"Please store this information in a safe place." & vbCrLf & vbCrLf & _
"Kind regards," & vbCrLf & vbCrLf & _
"Paul"
Dim OutApp As Outlook.Application
Dim objMsg As MailItem
Set OutApp = CreateObject("Outlook.Application")
Set objMsg = OutApp.CreateItem(olMailItem)
With objMsg
.Body = messageBody
.Display
End With
|
