+ Reply to Thread
Results 1 to 16 of 16

VBA Outlook to Write, but NOT send email

  1. #1
    Registered User
    Join Date
    12-19-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    20

    VBA Outlook to Write, but NOT send email

    Hello,

    I am trying to write a macro so that I can produce a roughly 60 line email that only needs minor adjustments before being sent out. I would like to just be able to run the macro and then trim the email as needed. It is not sent to the same person everytime, so I will not need the "to" section filled out, but the subject line is the same every time. I Would like to not use excel for this macro, if possible.

    Could someone please provide the code for this?

    Subject for the email is Info Request

    Let's just say the body of the email is as below,

    Hello Bob,

    Please provide the following:

    1. Phone Number
    2. Address
    3. Zip Code
    4. Client
    5. Fee

    Regards,


    Hopefully one of you can help me out in writing this code, Thanks A Ru

  2. #2
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: VBA Outlook to Write, but NOT send email

    Hi A Ru,

    The following macro can be run from Excel or from Outlook in module ModSimpleEmail:
    Please Login or Register  to view this content.
    To enable Macros, you probably have to go into the Trust Center:
    a. File > Options > Trust Center > Trust Center Settings > Macro Settings > Select a Macro Option > OK
    b. Close the Application and start it up again to activate the Macro Settings

    To load this macro into Excel or Outlook:
    a. Open Excel or Outlook
    b. 'Left Click' anywhere in the application (to get focus)
    c. Press 'Alt F11' to open up VBA
    d. Press 'Ctrl R' to open the 'Project' Window if it is not already open
    e. 'Right Click' the 'Project Window. > Insert > Module
    f. Optional. Press 'f4' to open the 'Properties' Window. Change the name to something other than 'Module1' if desired.
    g. Paste the code in the module
    h. When exiting select 'Yes' to permanently save.

    To run the Macro:
    a, Press 'Alt f8' to get the Macro list. Select the 'Macro', then select 'Run'.

    I hope this helps.

    Lewis

  3. #3
    Registered User
    Join Date
    12-19-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    20

    Re: VBA Outlook to Write, but NOT send email

    This does mostly what I need it to, but I was wondering if there is a faster way to insert my lines, rather than having to copy and paste each one, one at a time, and then put the line break after it? Also, I want the text to be numbered, for the example above I have five line numbers, lets say I take out line 4 for client, is there a way to make 5 revert to 4, without having to hand plug it in, in each email?

  4. #4
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: VBA Outlook to Write, but NOT send email

    Also, I want the text to be numbered, for the example above I have five line numbers, lets say I take out line 4 for client, is there a way to make 5 revert to 4
    Unfortunately, I do not use Outlook for Email (I just know how to access Outlook from VBA). My guess is that you probably want use Excel and then have several stock Email bodies in Excel, and select an Email body and possibly a recipient.

    If you need further help, I suggest you upload a sample Excel Workbook that contains text you want in your Email body.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    12-19-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    20

    Re: VBA Outlook to Write, but NOT send email

    I will find a way around the revert issue, by making it a number list instead of just listing the numbers. Our excel model locks me out of adding my outlook account to it, so I will need to develop it in Outlook only. Do you know how I could automatically add my signature at the end of your code?

  6. #6
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: VBA Outlook to Write, but NOT send email

    Hi,

    First of all you may not need VBA at all if you create an Outlook template for your Email.
    https://support.office.com/en-us/art...9-2dca68a96215
    https://support.office.com/en-us/art...b-55ee72b6bc2d

    The following code samples are based on the code in my previous post. It is assumed the the Email body is text only (no HTML).

    To use a text default signature:
    Please Login or Register  to view this content.
    To use a default signature that contains a picture:
    Please Login or Register  to view this content.
    Lewis

  7. #7
    Registered User
    Join Date
    12-19-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    20

    Re: VBA Outlook to Write, but NOT send email

    What's wrong?

    Please Login or Register  to view this content.
    Last edited by A Ru; 01-27-2017 at 06:13 PM.

  8. #8
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: VBA Outlook to Write, but NOT send email

    Sorry, I can't answer your question until you put CODE TAGS around your code. I will get in trouble and lose my privileges.

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

  9. #9
    Registered User
    Join Date
    12-19-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    20

    Re: VBA Outlook to Write, but NOT send email

    I updated please see above

  10. #10
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: VBA Outlook to Write, but NOT send email

    The underscore indicates a continuation line. You had an underscore on the last line of text which caused the problem. Remove the items in red below, and the compiler red will go away, and you are all set.
    Please Login or Register  to view this content.
    Please note that Excel limits the number of Continuation Lines in one string, but there is a workaround if you have a very long message.
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    12-19-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    20

    Re: VBA Outlook to Write, but NOT send email

    Could you tell me why It will not show up as line break like it did before? The signature works great though thank you!!! Also, if you have any advice on how to make the numbers show up in a number list so that they can revert to the correct number if needed I would appreciate that is well.

  12. #12
    Registered User
    Join Date
    12-19-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    20

    Re: VBA Outlook to Write, but NOT send email

    I figured out the list never mind on that, just need help with the line breaks

  13. #13
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: VBA Outlook to Write, but NOT send email

    Could you tell me why It will not show up as line break like it did before?
    I don't understand the question.

    Each vbCrLf creates a 'Carriage Return' and 'Linefeed' combination (2 not visible characters) as described in the obsolete teletype lexicon. If you have too much space between lines, sometimes replacing vbCrLf with vbLf might make the situation better.

    Please let me know if this does not answer your question. If you need more help, please describe the problem in more detail, or upload a picture of the problem.

  14. #14
    Registered User
    Join Date
    12-19-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    20

    Re: VBA Outlook to Write, but NOT send email

    Before I added in the signature it came up in the desired one term per line then goes to the next, while the other one kind of strings them all together, I think it could come from the sEmailBody as string part maybe. I cannot upload images right now
    Last edited by A Ru; 01-30-2017 at 03:57 PM.

  15. #15
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: VBA Outlook to Write, but NOT send email

    Before I added in the signature it came up in the desired one term per line then goes to the next, while the other one kind of strings them all together, I think it could come from the sEmailBody as string part

    You are probably correct. There are two different types of Email Body constructs .Body (used with text) and .HTMLBody used with HTML (which includes signatures containing pictures).

    Post #18 in the following thread may help you solve your problems: http://www.excelforum.com/showthread...t=#post4571583
    Last edited by LJMetzger; 01-30-2017 at 05:08 PM. Reason: Added the 'Missing Link'

  16. #16
    Registered User
    Join Date
    12-19-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    20

    Re: VBA Outlook to Write, but NOT send email

    In which thread? is there no way to use the body I had with, and add the Signature at the end?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Send email from shared Outlook email address
    By rousseauassociates in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-27-2016, 07:33 AM
  2. Send an email via outlook from and address other than your default email.
    By sungen99 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-28-2015, 06:15 PM
  3. Email Macro o send Outlook Email
    By asivaprakash in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-01-2013, 07:05 PM
  4. send selected range in email with default outlook email signature included
    By mdsickler in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-04-2013, 10:50 PM
  5. Send email via outlook
    By sakmsb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-29-2013, 11:14 PM
  6. Send email without using outlook
    By waspandbee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-05-2010, 08:48 AM
  7. [SOLVED] Can I record or write a macro to send a worksheet via Outlook?
    By Catherine in forum Excel General
    Replies: 1
    Last Post: 09-14-2005, 12:05 AM

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