How do you create a personalized, formatted email using Excel 2007 VBA

dbnelson9802

New Member
Joined
Aug 22, 2011
Messages
1
I am working on code (Excel 2007) that creates 200 plus emails using a template. I want to personalize the emails in Outlook 2007 (I.E. Dear John,) but also maintain formatting. I have had success doing one of the two items, but not both. If I use the generic message in the template with no personalization it will maintain the format, including colors, undelines, bold and indents. When I add in the Dear so and so part it causes everything to be in black regular default font. I thought maybe there would be a way to have code open the template each time I cycle through the If statement and update the template, but I am not aware of how I can do this. Please let me know how and if it can be done. Thanks and here is the Code:

Sub Create_Mail_From_List()

Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

Sheets("Email List").Select
On Error GoTo cleanup
For Each cell In Columns("C").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "D").Value) = "ready" Then
Set OutMail = OutApp.CreateItemFromTemplate("C:\Users\" & Application.UserName & "\Desktop\Implementations\IMPLEMENTATIONS EMAIL TEMPLATE.msg")
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Validation of Your Temporary Worker"
.Attachments.Add "C:\Users\" & Application.UserName & "\Desktop\Implementations\Attachments\" & Cells(cell.Row, "A").Value & ".xls"
.body = "Dear " & Cells(cell.Row, "B").Value & "," & OutMail.body
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Cells(cell.Row, "D").Value = "Sent"
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Forum statistics

Threads
1,214,968
Messages
6,122,509
Members
449,089
Latest member
RandomExceller01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top