Populate a form-based email using cells from an excel workbook

[matchooo]

Hello all.

Every day, I send out many outlook form-based emails. Each email I send requires me to copy/paste data from a spreadsheet into the message.

How can I modify my form so that it automatically populates certain data from a spreadsheet of my choice? Is there any other way I can automatically generate these emails?

Attached is the excel template where the data should be drawn from, as well as a picture of the email form where the data should be inserted (on second sheet of the workbook). They have been marked up with red to specify which data needs to be auto-populated.

Thanks in advance,
Matt

[Lonewolf]

hope this helps


Sub Send_Email()
Dim OutApp As Object
Dim OutMail As Object
dim Strbody as string

Application.ScreenUpdating = False

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

' the Range("").value is where you have the data that you need to be 'entered.
Strbody = "Dear " & Range("A1").value & vbcr & vbcr
Strbody = Strbody & "Your equipment has been shipped." & vbcr
Strbody = Strbody & "You will soon be contacted by of of our "& vbcr
Strbody = Strbody & "Please call us at (####)." & vbcr & vbcr
Strbody = Strbody & "Kinds and Regards." ) & vbcr & vbcr
Strbody = Strbody & "Matt"


On Error Resume Next
With OutMail
.To = range("B9").value
.CC = range("B20").value
.Subject = "Shipment Confirmation" & range("B3").value
.BCC = "default@default.com; default2@default.com"
.Body = Strbody
.Display 'or used .send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
application.ScreenUpdating = true
End Sub

[matchooo]

Sorry, I know essentially nothing about VBA. Where exactly do I paste this code?

[matchooo]

Works Great! Thanks!