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
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