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
Bookmarks