Results 1 to 3 of 3

Vba--excel to send outlook mail item

Threaded View

  1. #1
    Registered User
    Join Date
    10-22-2012
    Location
    Ottawa, ON
    MS-Off Ver
    Excel 2003
    Posts
    13

    Vba--excel to send outlook mail item

    I am new to VBA and searched everywhere for code to send an email from Excel. I finally located on which seemed to work. I am hoping you can help with a few things I need help with though. I have pasted the code below and bolded the sections which, when included cause the code to break down and not work. I do not get an error message, the email simply does not send. The debugger will not locate the problem, I only know that I did some customization with the code provided and when these sections are added, the code doesn’t work and when they are removed, it does work.

    1. I want to use code to choose the email address which is in the highlighted row as the recipient. The selection I am sending is Highlighted on the sheet by the user: it will be A2: E2 for example, where A is name, B is email, C is language, D is date and E is room. The highlighted row is correctly placed in the email with the great code, but is there a way to get the recipient drawn from B column?
    2.We have a general email box that we would like to send the email from—but every time I include this, it will not send. It only seems to work if it comes from my mailbox.
    3.We would like a delivery and a read receipt. I have tried to add this with code from another site, highlighted below, but it causes the code to fail and email will not send when I include it.

    Thanks in advance for any assistance you can provide on this,
    Kara

    Sub Send_Selection_Or_ActiveSheet_with_MailEnvelope()
    'Working in Excel 2002-2016
    Dim Sendrng As Range
    
    On Error GoTo StopMacro
    
    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With
    
    'Note: if the selection is one cell it will send the whole worksheet
    Set Sendrng = Selection
    
    'Create the mail and send it
    With Sendrng
    
    ActiveWorkbook.EnvelopeVisible = True
    With .Parent.MailEnvelope
    
    ' Set the optional introduction field thats adds
    ' some header text to the email body.
    .Introduction = "Thank you for your interest in Positive Space. This confirms your registration for the date, time and room NOTED BELOW. Please ensure you Print and bring the attachments in this email. No copies will be available at the training session. Please refrain from wearing scented products the day of the session. Should you require accommodation for this session, please advise us as soon as possible by email to [email protected]. We look forward to seeing you!"
    
    
    With .Item
    
    With OutMail
    .ReadReceiptRequested = True
    End With
    
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .From = "[email protected]"
    .Subject = "Formation: Espace Positif - Training: Positive Space"
    .Body = ActiveSheet.Range("A2").Text & ActiveSheet.Range("B2").Text & ActiveSheet.Range("C2").Text & ActiveSheet.Range("D2").Text & ActiveSheet.Range("E2").Text & vbCrLf
    .Attachments.Add "C:\POSITIVE SPACE\Positive Space.pptx"
    .Attachments.Add "C:\POSITIVE SPACE\Espace positif.pptx"
    .Attachments.Add "C:\POSITIVE SPACE\Positive Space participant manual.docx"
    .Attachments.Add "C:\POSITIVE SPACE\Espace positif manuel de participant.docx"
    .Attachments.Add "C:\POSITIVE SPACE\SupportFeb 2016(2).pptx"
    .Attachments.Add "C:\POSITIVE SPACE\Champion Feb 2016(2).pptx"
    .Send
    
    End With
    
    End With
    End With
    
    StopMacro:
    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With
    ActiveWorkbook.EnvelopeVisible = False
    
    End Sub
    Last edited by Leith Ross; 03-18-2016 at 02:11 PM. Reason: Added Code Tags

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Code to send mail copying from Excel to a new outlook mail.
    By paz1987 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-11-2015, 07:54 AM
  2. Need help: Excel VBA to moved Outlook mail item to a specific sub folder is Criteria is me
    By excelchamp25 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-06-2014, 06:50 AM
  3. Outlook 2007, Referencing mail Item created from outlook template
    By thusidie in forum Outlook Programming / VBA / Macros
    Replies: 2
    Last Post: 07-21-2014, 04:33 PM
  4. Macro to send e-mail through outlook from excel
    By goosetopher in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-15-2013, 12:19 AM
  5. Send e-mail with image on body (.html) on e-mail manager <> outlook
    By mariotnc in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-05-2012, 09:28 PM
  6. Send reminder mail from excel using outlook
    By arunsny in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-26-2011, 09:22 PM
  7. Need to send Outlook E-mail from Excel VBA without warnings.
    By Prz59 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-31-2005, 07:05 PM

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