Outlook Automation - Multiple Recipients

[Sunil Thomas]

Hello All,
I am new to VB/VBA, so please bear with me.
I am running a loop to add E-mail addresses to a string sCCEMailAddr (which can have multiple e-mail addresses when the loop is completed all received from a table). It just sends a confirmation e-mail to specific email from an access table. But I get a Run-Time error 13 with Type Mismatch for this string. The code for outlook is listed below.
Dim OlkApp As Outlook.Application
Dim OlkMsg As Outlook.MailItem
Dim OlkToRecip As Outlook.Recipient
Dim OlkCCRecip As Outlook.Recipient
Dim OlkCCRecipM As Outlook.Recipients ' Multiple recipients


With OlkMsg
'MsgAddress the "To" address of message

Set OlkToRecip = .Recipients.Add(sToEMailAddr)
OlkToRecip.Type = olTo

'CC portion
If c = 1 Then
Set OlkCCRecip = .Recipients.Add(sCCEMailAddr)
OlkCCRecip.Type = olCC
Else
Set OlkCCRecipM = .Recipients.Add(sCCEMailAddr) <--------------Error comes up here when I click debug
OlkCCRecipM.Type = olCC
End If

'Subject

.Subject = "RM Confirmation # " & glID

.Body = sBody

'Save the finished message in Draft.
.Save
.close olPromtForSave

End With

This script works if I am sending to one person in the To and CC, but fails when I have multiple address in CC portion. I could not find any helpful solution thus far on the internet.

Any help would be greatly appreciated.

Thanks in advance

 

[Sue Mosher [MVP-Outlook]]

Recipients.Add takes a single name or address as its argument, not a delimited string of addresses.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



Hello All,
I am new to VB/VBA, so please bear with me.
I am running a loop to add E-mail addresses to a string sCCEMailAddr (which can have multiple e-mail addresses when the loop is completed all received from a table). It just sends a confirmation e-mail to specific email from an access table. But I get a Run-Time error 13 with Type Mismatch for this string. The code for outlook is listed below.
Dim OlkApp As Outlook.Application
Dim OlkMsg As Outlook.MailItem
Dim OlkToRecip As Outlook.Recipient
Dim OlkCCRecip As Outlook.Recipient
Dim OlkCCRecipM As Outlook.Recipients ' Multiple recipients


With OlkMsg
'MsgAddress the "To" address of message

Set OlkToRecip = .Recipients.Add(sToEMailAddr)
OlkToRecip.Type = olTo

'CC portion
If c = 1 Then
Set OlkCCRecip = .Recipients.Add(sCCEMailAddr)
OlkCCRecip.Type = olCC
Else
Set OlkCCRecipM = .Recipients.Add(sCCEMailAddr) <--------------Error comes up here when I click debug
OlkCCRecipM.Type = olCC
End If

'Subject

.Subject = "RM Confirmation # " & glID

.Body = sBody

'Save the finished message in Draft.
.Save
.close olPromtForSave

End With

This script works if I am sending to one person in the To and CC, but fails when I have multiple address in CC portion. I could not find any helpful solution thus far on the internet.

Any help would be greatly appreciated.

Thanks in advance

 

[Sunil Thomas]

Then how would I add multiple addresses? Since I have to look in a table and
send it to as many as 10 e-mail addresses in the table.
Is there a way to create a distribution list in outlook through VBA?

Thanks for your help.

--
SUNIL THOMAS
Recipients.Add takes a single name or address as its argument, not a
delimited string of addresses.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



Hello All,
I am new to VB/VBA, so please bear with me.
I am running a loop to add E-mail addresses to a string sCCEMailAddr
(which can have multiple e-mail addresses when the loop is completed all
received from a table). It just sends a confirmation e-mail to specific
email from an access table. But I get a Run-Time error 13 with Type Mismatch
for this string. The code for outlook is listed below.
Dim OlkApp As Outlook.Application
Dim OlkMsg As Outlook.MailItem
Dim OlkToRecip As Outlook.Recipient
Dim OlkCCRecip As Outlook.Recipient
Dim OlkCCRecipM As Outlook.Recipients ' Multiple recipients


With OlkMsg
'MsgAddress the "To" address of message

Set OlkToRecip = .Recipients.Add(sToEMailAddr)
OlkToRecip.Type = olTo

'CC portion
If c = 1 Then
Set OlkCCRecip = .Recipients.Add(sCCEMailAddr)
OlkCCRecip.Type = olCC
Else
Set OlkCCRecipM = .Recipients.Add(sCCEMailAddr)
<--------------Error comes up here when I click debug
OlkCCRecipM.Type = olCC
End If

'Subject

.Subject = "RM Confirmation # " & glID

.Body = sBody

'Save the finished message in Draft.
.Save
.close olPromtForSave

End With

This script works if I am sending to one person in the To and CC, but fails
when I have multiple address in CC portion. I could not find any helpful
solution thus far on the internet.

Any help would be greatly appreciated.

Thanks in advance

 

[Sue Mosher [MVP-Outlook]]

Just repeat the Recipients.Add method as many times as needed, once for each address.

Yes, you can create a distribution list with the DistListItem.AddMembers method, but that would be overkill if all you want to do is send a message.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[jbrumbau]

Here is some code that avoids library references:

...
    Dim olApp As Object
    olApp = GetObject(, "Outlook.Application")
    Call SendOutlookEmail(olApp, subject, toRecip, ccRecip, bodyText)
...
Function SendOutlookEmail(ByRef olApp As Object, ByRef subject As String, ByRef toRecip() As String, ByRef ccRecip() As String, ByRef bodyText As String) As Boolean
On Error GoTo Err_Handler
    Dim objMail As Object
    Dim recip As String
    Dim i As Integer, numRecips As Integer
 
    Set objMail = olApp.createitem(0) 'OlItemType.olMailItem = 0
    With objMail
        numRecips = UBound(toRecip)
        For i = 1 To numRecips
            If LenB(toRecip(i)) > 0 Then
                .Recipients.Add toRecip(i)  'OlMailRecipientType.olTo = 1, default though so not required
            End If
        Next i
        numRecips = UBound(ccRecip)
        For i = 1 To numRecips
            If LenB(ccRecip(i)) > 0 Then
                .Recipients.Add(ccRecip(i)).Type = 2    'OlMailRecipientType.olCC = 2
            End If
        Next i
        .BodyFormat = 1 'OlBodyFormat.olFormatPlain = 1
        .subject = subject
        .Body = bodyText
        .send
    End With
    Set objMail = Nothing
    SendOutlookEmail = True
Exit_Handler:
    Exit Function
 
Err_Handler:
    If Err.Number < 0 Then  'Normally some large negative number if .send fails
        Call MsgBox("A contact is formatted incorrectly. Please correct the email address. Saving email to drafts.", vbExclamation, "Invalid Email Address")
        objMail.Save
        SendOutlookEmail = True
    Else
        MsgBox "Error sending email via Outlook."
        SendOutlookEmail = False
    End If
    Resume Exit_Handler
End Function