Solved

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

Posted on 2014-02-24
10
2,083 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 49

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 49

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 49

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
 

Author Comment

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

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
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…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

776 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