Consulting

Results 1 to 7 of 7

Thread: generating emails from excel fields

  1. #1

    generating emails from excel fields

    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

  2. #2
    VBAX Regular JP2R's Avatar
    Joined
    Feb 2008
    Posts
    37
    Location

    Generating Emails from Excel Fields

    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


    Quote Originally Posted by 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

  3. #3
    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

  4. #4
    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.

  5. #5
    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

  6. #6
    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?

  7. #7
    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


    [VBA]
    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

    [/VBA]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •