VBA Personalize Outlook Email

[whew1110]

Hi everyone!

I am trying to customize the body of an email blast. Ideally I would like to email to have "Dear ____" with each one specific to the client. I am able to get the text in however I would like to use HTML to format the font size, color, etc.

-----------------------------------------------------------------------------------------------------------
Sub emailreporting()

Dim emailaddy As String
Dim emailcc As String
Dim acctname As String
Dim acctno As String
Dim EmailSubject As String
Dim dateday As String
Dim datemonth As String
Dim dateyear As String
Dim verifyday As String
Dim resp As String
Dim resptype As String
Dim Dear As String


Cells.Find(What:="Email Subject").Activate
EmailSubject = ActiveCell.Offset(0, 1).Value
resptype = ActiveCell.Offset(1, 1).Value
Cells.Find(What:=resptype & "Dear", SearchOrder:=xlByColumns).Activate
ActiveCell.Offset(1, 0).Select


Do

acctname = ActiveCell.Offset(0, -1).Value
acctno = ActiveCell.Value
emailaddy = ActiveCell.Offset(0, 1).Value
emailcc = ActiveCell.Offset(0, 2).Value
Dear = ActiveCell.Offset(0, 0).Value

Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = emailaddy
.cc = emailcc
.Subject = EmailSubject
.HTMLBody = Dear & "<p style='color:rgb(0,0,0);font-family:arial;font-size:12'>

-----------------------------------------------------------------------------------------------------------

You see how String Dear is used at the beginning of .HTMLBody. How can I move the string Dear into the html coding so that it will have the same font color, size, etc. applied. currently it is outside of the HTML style script. or if there is an alternate way to get the "Dear ___", formatted to the same was as the rest of the email body.

thanks!!!!

[pjwhitfield]

Please Login or Register  to view this content.

[whew1110]

Thanks PJ!

I tried putting in your code but it just came out with 'Dear' no longer referencing the string and text only. Any other alternative you can think of?

[pjwhitfield]

Im not sure if Im missing something, as far as I can see you simply need move the formatting string to the start of the .HTMLBody and then follow with whatever data you were already using.

Please Login or Register  to view this content.