Solved

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

Posted on 2014-02-24
10
1,996 Views
Last Modified: 2014-03-15
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
0
Comment
Question by:jmsjms
  • 6
  • 3
10 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39882212
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
0
 

Author Comment

by:jmsjms
ID: 39882235
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
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39882251
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

0
 

Author Comment

by:jmsjms
ID: 39882257
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. :-)
0
 
LVL 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 500 total points
ID: 39882273
In that case I would recommend to make a mailmerge with word with the XL file as data source

refer to

http://office.microsoft.com/en-us/word-help/use-mail-merge-to-send-personalized-e-mail-messages-to-your-e-mail-address-list-HA010109162.aspx
Regards
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:jmsjms
ID: 39882279
Does that output a word document thats emailed or a native HTML email?
0
 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
ID: 39884146
It will be in HTML with the normal word excess garbage
0
 

Author Comment

by:jmsjms
ID: 39884980
So all in all, there's no simple way to create a formatted Customised Eshot just with MS Office, unless you use Word?
0
 

Accepted Solution

by:
jmsjms earned 0 total points
ID: 39917385
In the end I found this tool that works with the WOrd mail merge.  It's probably just a GUI for a script but it's great for office users.

http://www.mapilab.com/outlook/mail_merge/
0
 

Author Closing Comment

by:jmsjms
ID: 39931081
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)
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Set OWA language and time zone in Exchange for individuals, all users or per database.
Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now