generating emails from excel fields

[zach]

I'm wondering if anyone can direct me to an excel macro that generates form emails from the fields of a spreadsheet. This should be pretty simple, but I'm trying to save time by working from a template. The only thing I'm not sure how to do is make it look up the recipient's name in my firm's global address book. Any help would be appreciated.

Thanks,

Zach

[JP2R]

Zach -

I don't know much by question...
Here's what I've done...

I'm using a Dell Laptop with XP
Microsoft Office 2007

I have a spreadsheet that has several columns:

User
User email
Moved From Room
Moved To Room
Blah blah blah...

If you go to Word - on the ribbon you will find the option for "Mailings" - select that tab and you will see the ribbon offers "Start Mail Merge" - I like to use the step-by-step wizard...keeps me on track...

You can save the document as a template if you wish...

Here's the deal...

Using the wizard it will ask you where to obtain the email addresses - and you select the option to browse - choose the Excel spreadsheet that has those fields you want to merge - and the you can put them any where you want in the Word document...

I hope that I'm being clear - sort of hard to explain without pictures (LOL)

-- Jae



I'm wondering if anyone can direct me to an excel macro that generates form emails from the fields of a spreadsheet. This should be pretty simple, but I'm trying to save time by working from a template. The only thing I'm not sure how to do is make it look up the recipient's name in my firm's global address book. Any help would be appreciated.

Thanks,

Zach

[IrishCharm]

Hi Zach,

if you're looking to generate emails from Excel i have a piece of code which will do that for you. You need to save your html signature down to the C drive for it to work.

I have attached a sample html sig file - right click on it to edit it to what you need. basically if you tweek it to point at Excel it should work for you.

There is a small issue with this at the moment as there are unknown symbols appearing also - it is currently a WIP if you check my code which i posted above you.

Sarah

[zach]

Jae- I'm familiar with mail merge, that is not at all what I'm going for.

Sarah- I'm not really sure what you're talking about with html signatures; I'm writing a VBA macro. I also don't see any attachments, which could be part of my problem understanding this.

[IrishCharm]

Hi Zach,

I understood that you want to create a macro whereby in Excel you can have a list of email addresses, attachments, etc which you can send via email from Excel. The code i sent on allows an email to be sent from within VB code, and with a bit of tweeking this can attach links from spreadsheets, email to several people, etc.

Apologies if i got the wrong end of your posting - also not sure what happened to my attachment!!!

Sarah


Sarah

[zach]

Got it. I just didn't understand what you meant with the whole html signature thing. Maybe you can just paste the code into your post?

[IrishCharm]

Hi,

i have amended the first lot of code i sent on so you can include attachments etc. if you are looking to send to a list of people let me know and i can set something up and send it to you as .xls attachment to several email addresses all at once, if you are having difficulty with it. let me know if you have any issues with this.

There is one teeny tweek with the code which im working on fixing - it inserts some sort of symbol which i am trying to fix up but its only small.

Sarah



Sub SendMessage()


str_subject = "HELLO WORLD"
str_body = "TEST MAIL"

'' THIS BIT IS ONLY NEEDED IF YOU ARE SENDING A .HTML SIGNATURE
SigString = "C:\Documents and Settings\duffsa\Application Data\Microsoft\Signatures\MySig.htm"
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(SigString).openastextstream(1, -2)
Signature = ts.readall


Set objOutlook = CreateObject("outlook.application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)


'' THIS IS NEEDED ONLY IF YOU ARE SENDING .XLS ATTACHMENTS - IT SAVES THE FILE TO THE C DRIVE THEN ATTACHES FROM THERE.
str_reportname = "FILE TO ATTACH"
Workbooks.Open str_reportname
Set WB = ActiveWorkbook
On Error Resume Next
Kill "C:\" & str_reportname
On Error GoTo 0
WB.SaveAs FileName:="C:\" & str_reportname

With objOutlookMsg

'' WHERE I HAVE ONE EMAIL ADDRESS HERE YOU CAN DECLARE A STRING AND PUT IN MANY ADDRESSES
Set objOutlookRecip = .Recipients.Add("MY EMAIL ADDRESS")

'' YOU CAN DECLARE THIS SUBJECT ANYWHERE IN THE SPREADSHEET OR WITHIN THE MACRO ITSELF
.Subject = str_subject

'' YOU CAN USE .BODY FOR NORMAL TEXT WITHIN THE BODY BUT IF YOU WANT TO INCLUDE YOUR SIG THEN YOU NEED .HTMLBODY AS BELOW
.htmlBody = "<html><body>" & str_body & "<br><br>" & Signature

'' THIS IS NEEDEED ONLY IF YOU ARE SENDING AN .XLS ATTACHMENT
.Attachments.Add WB.FullName

For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

''CHANGE THE DISPLAY TO SEND TO EMAIL RATHER THAN DISPLAY IT - .Send will send the mail
.Display

End With

End Sub