Sending message with multiple SMTP BCCs

  • Thread starter YnJhdm9mb3h0cm90dWs
  • Start date
Status
Not open for further replies.
Y

YnJhdm9mb3h0cm90dWs

I'm attempting to use the following VBA from within Access to send emails:

Sub SendMessage(DisplayMsg As Boolean, MailTo As String, Optional

MailCC As String, Optional MailBCC As String, _

Optional Subject As String, Optional Body As String, Optional

AttachmentPath)

Dim objOutlook As Outlook.Application

Dim objOutlookMsg As Outlook.MailItem

Dim objOutlookRecip As Outlook.Recipient

Dim objOutlookAttach As Outlook.Attachment

' Create the Outlook session.

Set objOutlook = CreateObject("Outlook.Application")

' Create the message.

Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg

' Add the To recipient(s) to the message.

Set objOutlookRecip = .Recipients.Add(MailTo)

objOutlookRecip.Type = olTo

' Add the CC recipient(s) to the message.

Set objOutlookRecip = .Recipients.Add(MailCC)

objOutlookRecip.Type = olCC

' Add the BCC recipient(s) to the message.

Set objOutlookRecip = .Recipients.Add(MailBCC)

objOutlookRecip.Type = olBCC

' Resolve each Recipient's name.

For Each objOutlookRecip In .Recipients

objOutlookRecip.Resolve

Next

' Set the Subject, Body, and Importance of the message.

> Subject = Subject

> Body = Body & vbCrLf & vbCrLf

> Importance = olImportanceLow 'Low importance

' Add attachments to the message.

If Not IsMissing(AttachmentPath) Then

Set objOutlookAttach = .Attachments.Add(AttachmentPath)

End If

' Should we display the message before sending?

If DisplayMsg Then

> Display

Else

> Save

> Send

End If

End With

Set objOutlook = Nothing

Set objOutlookMsg = Nothing

End Sub

This sub gets called from within Access to pass forward a list of BCCs.

If I enter the following in the immediate window:

SendMessage false, "userone@hotmail.com",,"0987890@hotmail.co.uk","Testing",

"hello"

it works and the message is placed in the outbox.

But if I add another address to the BCC field such as:

SendMessage false, "userone@hotmail.com",,"0987890@hotmail.co.uk;

123456@hotmail.com","Testing", "hello" -- it will save it but won't place it

in the outbox, then when I open the message and click 'Send' button, it goes

to the outbox! This also occurs if 'false' is made 'true' in the line above.

The addresses are pulled from within the Access tables and will not appear

in the user's Outlook address book.

I'm not that familiar with Outlook, but it seems to be to do with resolving

the SMTP addresses. Can anyone give me a helping hand here?
 
S

Sue Mosher [MVP]

Your code doesn't allow for multiple addresses. You would need to parse each

string, breaking it apart at the semicolon delimiter, and call

Recipients.Add for each address.

Alternatively, if you're always passing SMTP addresses, you can skip

Recipients.Add and set the value of the To, CC, and Bcc properties to each

corresponding address string, which can contain multiple addresses.

Sue Mosher

"bravofoxtrotuk" <bravofoxtrotuk> wrote in message

news:B08ED573-84FB-4973-A4A0-24C5C3CA6997@microsoft.com...
> I'm attempting to use the following VBA from within Access to send emails:

> Sub SendMessage(DisplayMsg As Boolean, MailTo As String, Optional
> MailCC As String, Optional MailBCC As String, _
> Optional Subject As String, Optional Body As String, Optional
> AttachmentPath)
> Dim objOutlook As Outlook.Application
> Dim objOutlookMsg As Outlook.MailItem
> Dim objOutlookRecip As Outlook.Recipient
> Dim objOutlookAttach As Outlook.Attachment

> ' Create the Outlook session.
> Set objOutlook = CreateObject("Outlook.Application")

> ' Create the message.
> Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

> With objOutlookMsg
> ' Add the To recipient(s) to the message.
> Set objOutlookRecip = .Recipients.Add(MailTo)
> objOutlookRecip.Type = olTo

> ' Add the CC recipient(s) to the message.
> Set objOutlookRecip = .Recipients.Add(MailCC)
> objOutlookRecip.Type = olCC

> ' Add the BCC recipient(s) to the message.
> Set objOutlookRecip = .Recipients.Add(MailBCC)
> objOutlookRecip.Type = olBCC

> ' Resolve each Recipient's name.
> For Each objOutlookRecip In .Recipients
> objOutlookRecip.Resolve
> Next

> ' Set the Subject, Body, and Importance of the message.
> .Subject = Subject
> .Body = Body & vbCrLf & vbCrLf
> .Importance = olImportanceLow 'Low importance

> ' Add attachments to the message.
> If Not IsMissing(AttachmentPath) Then
> Set objOutlookAttach = .Attachments.Add(AttachmentPath)
> End If

> ' Should we display the message before sending?
> If DisplayMsg Then
> .Display
> Else
> .Save
> .Send
> End If
> End With
> Set objOutlook = Nothing
> Set objOutlookMsg = Nothing
> End Sub

> This sub gets called from within Access to pass forward a list of BCCs.

> If I enter the following in the immediate window:

> SendMessage false,
> "userone@hotmail.com",,"0987890@hotmail.co.uk","Testing",
> "hello"

> it works and the message is placed in the outbox.

> But if I add another address to the BCC field such as:

> SendMessage false, "userone@hotmail.com",,"0987890@hotmail.co.uk;
> 123456@hotmail.com","Testing", "hello" -- it will save it but won't place
> it
> in the outbox, then when I open the message and click 'Send' button, it
> goes
> to the outbox! This also occurs if 'false' is made 'true' in the line
> above.

> The addresses are pulled from within the Access tables and will not appear
> in the user's Outlook address book.

> I'm not that familiar with Outlook, but it seems to be to do with
> resolving
> the SMTP addresses. Can anyone give me a helping hand here?
 
Y

YnJhdm9mb3h0cm90dWs

Sue,

d'oh!

Thanks for that. They will/should always be SMTP addresses, and ironically

I've already assembled the addresses with the semi-colons within Access

before calling this sub. But never mind - I had a quick scout elsewhere on

the forum and have now modified the bits of my code below and it works a

treat! (in case anyone else needs the same)

Bob

====================================

' Add the To recipient(s) to the message.

objOutlookMsg.To = (MailTo)

objOutlookRecip.Type = olTo

' Add the CC recipient(s) to the message.

objOutlookMsg.CC = (MailCC)

objOutlookRecip.Type = olCC

' Add the BCC recipient(s) to the message.

objOutlookMsg.BCC = (MailBCC)

objOutlookRecip.Type = olBCC

' Resolve each Recipient's name.

For Each objOutlookRecip In .Recipients

objOutlookRecip.Resolve

Next

"Sue Mosher [MVP]" wrote:


> Your code doesn't allow for multiple addresses. You would need to parse each
> string, breaking it apart at the semicolon delimiter, and call
> Recipients.Add for each address.

> Alternatively, if you're always passing SMTP addresses, you can skip
> Recipients.Add and set the value of the To, CC, and Bcc properties to each
> corresponding address string, which can contain multiple addresses.

> > Sue Mosher
> > >

> "bravofoxtrotuk" <bravofoxtrotuk> wrote in message
> news:B08ED573-84FB-4973-A4A0-24C5C3CA6997@microsoft.com...
> > I'm attempting to use the following VBA from within Access to send emails:
> > Sub SendMessage(DisplayMsg As Boolean, MailTo As String, Optional
> > MailCC As String, Optional MailBCC As String, _
> > Optional Subject As String, Optional Body As String, Optional
> > AttachmentPath)
> > Dim objOutlook As Outlook.Application
> > Dim objOutlookMsg As Outlook.MailItem
> > Dim objOutlookRecip As Outlook.Recipient
> > Dim objOutlookAttach As Outlook.Attachment
> > ' Create the Outlook session.
> > Set objOutlook = CreateObject("Outlook.Application")
> > ' Create the message.
> > Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
> > With objOutlookMsg
> > ' Add the To recipient(s) to the message.
> > Set objOutlookRecip = .Recipients.Add(MailTo)
> > objOutlookRecip.Type = olTo
> > ' Add the CC recipient(s) to the message.
> > Set objOutlookRecip = .Recipients.Add(MailCC)
> > objOutlookRecip.Type = olCC
> > ' Add the BCC recipient(s) to the message.
> > Set objOutlookRecip = .Recipients.Add(MailBCC)
> > objOutlookRecip.Type = olBCC
> > ' Resolve each Recipient's name.
> > For Each objOutlookRecip In .Recipients
> > objOutlookRecip.Resolve
> > Next
> > ' Set the Subject, Body, and Importance of the message.
> > .Subject = Subject
> > .Body = Body & vbCrLf & vbCrLf
> > .Importance = olImportanceLow 'Low importance
> > ' Add attachments to the message.
> > If Not IsMissing(AttachmentPath) Then
> > Set objOutlookAttach = .Attachments.Add(AttachmentPath)
> > End If
> > ' Should we display the message before sending?
> > If DisplayMsg Then
> > .Display
> > Else
> > .Save
> > .Send
> > End If
> > End With
> > Set objOutlook = Nothing
> > Set objOutlookMsg = Nothing
> > End Sub
> > This sub gets called from within Access to pass forward a list of BCCs.
> > If I enter the following in the immediate window:
> > SendMessage false,
> > "userone@hotmail.com",,"0987890@hotmail.co.uk","Testing",
> > "hello"
> > it works and the message is placed in the outbox.
> > But if I add another address to the BCC field such as:
> > SendMessage false, "userone@hotmail.com",,"0987890@hotmail.co.uk;
> > 123456@hotmail.com","Testing", "hello" -- it will save it but won't place
> > it
> > in the outbox, then when I open the message and click 'Send' button, it
> > goes
> > to the outbox! This also occurs if 'false' is made 'true' in the line
> > above.
> > The addresses are pulled from within the Access tables and will not appear
> > in the user's Outlook address book.
> > I'm not that familiar with Outlook, but it seems to be to do with
> > resolving
> > the SMTP addresses. Can anyone give me a helping hand here?


>
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
A Flag Message for Follow Up after sending Outlook VBA and Custom Forms 1
R Warn before sending message Outlook VBA and Custom Forms 4
Diane Poremsky Automatically create a task when sending a message Using Outlook 0
Diane Poremsky Select from a List of Subjects before Sending a Message Using Outlook 0
Diane Poremsky To delay sending a message in Outlook Using Outlook 0
Diane Poremsky Check for missing attachments before sending a message Using Outlook 1
Diane Poremsky Add Attachment Names to Message Before Sending Using Outlook 0
A Code to automatically delete message after sending Outlook VBA and Custom Forms 6
T What code to use to move the custom field values to message body when sending e-mail ? Outlook VBA and Custom Forms 8
Diane Poremsky Check Message Size Before Sending Using Outlook 0
Diane Poremsky To delay sending a message in Outlook Using Outlook 1
Diane Poremsky Add Secure to the Message Subject before Sending Using Outlook 0
A Tentative accept a meeting without sending a message Outlook VBA and Custom Forms 2
X Outlook 2003 not sending all of the new email message Using Outlook 1
M How to forward a message and delay sending for up to 30 days Using Outlook 8
J Outlook 2007 Failure to Send Message<=Sending reported Error 0x80040201 Using Outlook 1
D Line through message instead of sending to Delete folder??? Using Outlook 3
R Sending sms message via Outlook 2010 issue Using Outlook 12
D Can I set a specific account for sending a message ? Outlook VBA and Custom Forms 1
M Got error message when sending email:Could not complete the operat Outlook VBA and Custom Forms 1
T Sending Error Message Outlook VBA and Custom Forms 2
P default font when sending email from browser Using Outlook 1
M Messages Intermittently Dont Arrive In Sent Items After Sending Successfully Using Outlook 4
O Outlook on Android: after sharing / sending a news article, draft remains open. Why? Using Outlook 1
P Sending email from outlook IMAP to GMAIL where embedded images are added as attachment Using Outlook 1
V Form data not sending for some users Outlook VBA and Custom Forms 2
L isn't there an OL add-on that flags addressee before sending Using Outlook 3
R Microsoft Outlook 2016 - Gmail not sending, asks for password for SMTP, tried different ports Using Outlook 23
D Sending email from Office 365 alias in Outlook Using Outlook 3
E Change sending account depending on Subjectline Outlook VBA and Custom Forms 0
L unblocking attachments before sending Office 365 Advanced Protection Using Outlook 0
B Outlook 2003 email sending & receiving suddenly stopped working Using Outlook 3
HarvMan Hotmail - Sending email is undeliverable Using Outlook 4
A Sending contact vcards sends older version instead of updated version Using Outlook 4
M McAllister Outlook stops Sending/Receiving/Synching after disconnecting remote desktop session Using Outlook 2
D Adding Enterprise Exchange Email Account to Outlook Prevents Sending via Outlook.com Account Using Outlook.com accounts in Outlook 10
B When sending an email, I am showing 2 of my address's Using Outlook 1
M Auto expand Distribution List Before Sending Email Outlook VBA and Custom Forms 1
A Check for words in subject header before sending email Outlook VBA and Custom Forms 4
O Run macro automatically at sending an email Using Outlook 11
W Sending To Wrong Account Using Outlook 15
H Select Specific Account When Sending Email, Based on Current Folder Outlook VBA and Custom Forms 1
M Help sending email but removing signature via VBA Outlook VBA and Custom Forms 5
M MsgBox when not sending from specified account Outlook VBA and Custom Forms 2
A Script to fetch data from mails in restricted collection and sending them to excel Using Outlook 1
R Sending emails via Outlook XP, from Windows 10 File Explorer Using Outlook 1
L Creating drafts when I thought I was sending Using Outlook 1
C address book "when sending email" bug? Using Outlook 0
H Custom autoforwarding, sending mail through outlook office 365 Using Outlook 1
R Sending email copy (*.msg file) of sent email if subject line contains specific string. Outlook VBA and Custom Forms 1

Similar threads

Top