+ Reply to Thread
Results 1 to 6 of 6

Using Excel VBA to generate an email in outlook

  1. #1
    Registered User
    Join Date
    07-31-2010
    Location
    Pawtucket, Rhode Island, USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Using Excel VBA to generate an email in outlook

    Hello, I am trying to create a macro (in excel) that generates an email. I have the VBA code for generating the email but I wanted to have a few features in it. Emailing from a macro seems to be very challenging for me and, to be honest, I just recently learned VBA, so I try my hardest to get it right, but it doesn't always come out the way I plan. Here is what I am looking for:

    1. I have several Input boxes for the user, one of which asks to enter a name. In the email I wish to have that string (called "String4" below) to appear bold in the message, but not have the whole body appear in bold. (Also, on that topic, is there a way to change the color of certain words in that same email?)

    2. I would like the option 'have replies sent to' changed to a different email address.

    3. I would like to insert a mailto hyperlink in a sentence in the message, such as "Please contact the Sales Group for question (where "Sales Group" has an embedded hyperlink mailto:[email protected] that the receiver can click on).

    Is there a way to do this? I have this as the code (but the outcome is in text format - and I think I need to have it in HTML format). Below is the example of the private sub and I have dim'd strings already in previous subs in the macro (the Strings indicated below are more descriptive in my macro, but I am listing them as String1, String2, etc., so you know where they are).

    Please Login or Register  to view this content.


    Sorry for all the questions and I hope this makes sense. Thank you for your time.

    Brian
    Last edited by BrianDegnan; 07-31-2010 at 05:04 PM. Reason: Clarity

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Using Excel VBA to generate an email in outlook

    Brian

    You have the code for creating a basic text email message.

    For 1 & 3 what you want to do will require that you create an HTML mail message, which I see you've mentioned already.

    So you would need to construct the relevant HTML code incorporating your variables.

    For 2 I'm not so sure, there might be a property of the MailItem object you could use.

    I've had a look for one but found nothing so far.

  3. #3
    Registered User
    Join Date
    07-31-2010
    Location
    Pawtucket, Rhode Island, USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Using Excel VBA to generate an email in outlook

    Thank you, Norie. I tried ".HTMLBody" but when I enter <b>...</b> around String4, but VBA is giving me an error --> "Compile Error: Expected: expression." Do you know how is should be written? Is ".HTMLBody" the right code to start with? I do not know any of the HTML codes - except this one which I happened to stumble upon online. Does the rest of the email code that I wrote need to be changed as well to be in 'html' format or is it fine?

    Also, I do not know how to write the html expression for hyperlink. I've seen "^k" but the caret (^) is a "Ctrl" notation and I do not know how to fit it in here or even if that is the right approach to addressing the issue. I need the phrase "Sales Group" to show in the email but for "mailto:[email protected]" to be embedded in it. I don't know where to start on that.

    Again, thank you!
    Last edited by BrianDegnan; 07-31-2010 at 08:07 PM.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Using Excel VBA to generate an email in outlook

    The link below will give you the basic HTML mark up tags:

    http://www.w3schools.com/tags/default.asp

    In terms of hyperlinks etc.. in HTML terms:

    Please Login or Register  to view this content.
    In terms of embedding HTML tags into your string

    Please Login or Register  to view this content.
    remember that where you need to use " literally within the string you must double up (else use Chr 34), eg:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-31-2010
    Location
    Pawtucket, Rhode Island, USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Using Excel VBA to generate an email in outlook

    Great, thank you. Do you know if there is a way to have replies sent to a different email address?

    Thank you, again!

  6. #6
    Registered User
    Join Date
    07-31-2010
    Location
    Pawtucket, Rhode Island, USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Using Excel VBA to generate an email in outlook

    Actually, i think i found it:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1