Sending message with multiple SMTP BCCs

  • Thread starter Thread starter bravofoxtrotuk
  • Start date Start date
B

bravofoxtrotuk

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, "(e-mail address removed)",,"(e-mail address removed)","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, "(e-mail address removed)",,"(e-mail address removed);
(e-mail address removed)","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?
 
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,

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
 
Back
Top