Sending message with multiple SMTP BCCs

  • Thread starter YnJhdm9mb3h0cm90dWs
  • Start date Views 2,020
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?


>
 

Top