Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Open an outlook email and populate the body of the email







             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

Contact Form

Name

Email *

Message *

Popular Posts