Link to home
Start Free TrialLog in
Avatar of jmsjms
jmsjmsFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How can you do a mail shot from outlook using data in Excel (Office 2007)

Hi,

We've got a list of contacts we need to send an update too.

The contacts are listed in Excel.  We use Outlook 2007 (with a few 2010's).

How can we do this?

I'd rather not use word as we're trying to keep it simple.

THanks
Avatar of Rgonzo1971
Rgonzo1971

Hi,

pls try

Sub Macro()

Set OL = CreateObject("Outlook.Application")

For Each xlRecipient In Range("A1:A5")

    Set MailSendItem = OL.CreateItem(olMailItem)
    With MailSendItem
        .Subject = "Your Subject"
        .Body = "Your BodyText"
        .To = xlRecipient
        .Display
        '.Send
    End With
Next
Set OL = Nothing

End Sub

Open in new window

Regards
Avatar of jmsjms

ASKER

Thanks but the email shot needs to be formatted with the Recipients name in.

Dear [Name]

Blah, blah, blah...

etc.

So we need to put in fields and customisation.

Is there anyway to do this in Outlook?

Cheers
see example if the name is in the C Column you could do it like that

Sub Macro()

Set OL = CreateObject("Outlook.Application")

For Each xlRecipient In Range("A1:A25")

    Set MailSendItem = OL.CreateItem(olMailItem)
    With MailSendItem
        .Subject = "Your Subject"
        .Body = "Dear " & xlRecipient.Offset(0,2) & "," & vbCrLf & "Rest of Your BodyText"
        .To = xlRecipient
        .Display
        '.Send
    End With
Next
Set OL = Nothing

End Sub

Open in new window

Avatar of jmsjms

ASKER

Hi Rgonzo,

Thanks for the info.  This is really good for tech stuff, but I'm trying to source something for the marketing department so it needs to to WYSIWYG and for them to put pics in, formatted text etc, rather than a script process.  They would just pull their collective hair out using script.

Sorry should have noted this in the question. :-)
SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jmsjms

ASKER

Does that output a word document thats emailed or a native HTML email?
It will be in HTML with the normal word excess garbage
Avatar of jmsjms

ASKER

So all in all, there's no simple way to create a formatted Customised Eshot just with MS Office, unless you use Word?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jmsjms

ASKER

I've marked my comment as a solution as the tool I found allowed us to send mail merges with attachments and made it easy for staff.

All points to Rgonzo1971 as he confirmed that word has to be used as part of the process for a mail merge (if you dont use a mail merge system)