Newsletter Code / Customize loop

[balacio]

Hello,

I found this code on a blogspot belonging to Milos Holovsky.
Macros in Excel | What is an Excel Macro?: VBA to send a Outlook Draft Email to a list of emails in Excel

I am a complete beginner and I need your help.

This macro will loop through a list of emails addresses and send a template.
I manage to fiddle a bit with it to do what I want it to do.
Now the last thing I need from it is to customize the email title with a name which 2 cells before on the same line.

How can I do that?

Pls help!

Thanks,
JA

Here is teh code:

Sub TestEmail()


Dim OutMail As Object
Dim MyItem As Object


Sheets("Clients").Activate


For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" Then


Set OutMail = CreateObject("Outlook.Application")
Set MyItem = OutMail.CreateItemFromTemplate("C:\Users\Milos\AppData\Roaming\Microsoft\Templates\Newsletter.oft")


With MyItem
.To = cell.Value
.Subject = "Newsletter"


.Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s"
End With
On Error GoTo 0
Set OutMail = Nothing
Set MyItem = Nothing

End If
Next cell


On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing





Set MyItem = Nothing


cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True


End Sub

 

[tweedle]

Hi, Welcome to the forum.

By 'title', do you mean subject line of the e-mail?
And then where, in relation to any given e-mail address in column B, is the text you want to add to the e-mail?

 

[balacio]

Hello tweedle,

Thanks for the quick reply and apologies for the lack of clarity.

Basically I would like to customize the Subject of the email.

For the moment it would only show "Newsletter" (If I uncomment the line .Subject) Or the title I saved in the template.
Ideally I would love to declare "Dim MailSubject As String", so I can write the title in my sheet and add to it the name of my client.
The name of the client is on column "B" same line as email address picked up in the code.
I guess the code would like something along
".Subject = MailSubject & (that's the part I have no clue about)"

Thanks again everyone for your help.

J

Here is how I fiddled with the code:

Sub TestEmail()

' Thanks to Milos Holovsky
' Original code on what-is-an-excel-macro.blogspot.co.uk/2012/09/vba-to-send-outlook-draft-email-to-list.html


Dim OutMail As Object
Dim MyItem As Object


Dim MailPath As String
Dim MailFile As String
'Dim MailSubject As String


MailPath = ActiveWorkbook.ActiveSheet.Range("B4").Value
MailFile = ActiveWorkbook.ActiveSheet.Range("B5").Value
'MailSubject = ActiveWorkbook.ActiveSheet.Range("B6").Value





ActiveWorkbook.ActiveSheet.Select


For Each cell In Columns("H").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" Then


Set OutMail = CreateObject("Outlook.Application")
Set MyItem = OutMail.CreateItemFromTemplate(MailPath & MailFile)


With MyItem
.To = cell.Value
' .Subject = MailSubject

.Display
Application.Wait (Now + TimeValue("0:00:01"))
Application.SendKeys "%s"
End With
On Error GoTo 0
Set OutMail = Nothing
Set MyItem = Nothing

End If
Next cell


On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing





Set MyItem = Nothing


cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True


End Sub

 

[tweedle]

OK - Look at the Offset function of the range cell (Column Hm it looks like)


MailSubject = "Newsletter " & cell.Offset(0,-6).Text ' Referring back to column B from Column H (which seems to be your e-mail addresses)...

See how that works for you...

 

[balacio]

You are the Man!
Thank you very much!