Excel VBA: Pass value of string variable to variable of another type

[dougdrex]

I'm using Excel 2010.

Maybe this is something that cannot be done because I have searched the internet for instructions, but to no avail.

I have a string variable that gets entered into the "Cc" field of an email. However, before I send out the email message (through Outlook), I need to check it in our internal global address book to make sure it is a valid ID (sometimes employees leave the company) so it won't cause an error message.

In order to use the .ResolveAll command, the value needs to be in a variable defined as an Outlook.Recipient (at least that is my understanding). How can I pass the value of the string variable to the other variable without it generating an error (I've tried several methods).

If posting the significant portions of my code would help, please let me know.


Thanks!
Doug

 

[Norie]

Doug

Are you adding recipients to the email?

You can do that using the Recepients collection.

Dim olEmail As Object
Dim olRecpt As Object

    Set olEmail = Application.CreateItem(0) ' olMailItem

    Set olRecpt = olEmail.Recipients.Add ("Jon Grande") 

    olRecpt.Type = 2 ' olCC

You should then be able to use ResolveAll like this.

If olEmaile.Recipients.ResolveAll Then
    MsgBox "All recipients resolved."
Else
    MsgBox "All recipients not resolved."
    Exit Sub
End If

' code to send email

PS If all recipients aren't resolved you can loop through the collection to find which ones weren't.

For Each olRecpt In olEmail.Recipients
    If Not olRecpt.Resolved Then
        MsgBox olRecpt.Name
    End If
Next olRecpt

 

[dougdrex]

Hi Norie,

So I may have created my macro in a way that is less than optimal. Perhaps you can help answer that question. Let me try to summarize what I need to accomplish. If more information is required, just let me know.

I have two columns where employee IDs are provided. One column will need to be inserted in the "To" field of an email, while the values in the other column will be inserted in the "Cc" field. There are other details, but they may be unnecessary for the purpose of this discussion. I need to figure out a way to identify when one or more of the values is not recognized by Outlook and display the Employee IDs to the user.

Below is the code where I am declaring and defining the variables being used for creating and sending the emails.

    Dim OlApp As Outlook.Application
    Dim OlNS As Outlook.Namespace
    Dim OlFolder As Outlook.MAPIFolder
    Dim OlMailItem As Outlook.MailItem
    Dim strMaker As String
    Dim strChecker As String

    strMaker = GetVisibleMaker(Range("H2", Cells(Rows.Count, "H").End(xlUp)))
    strChecker = strAuthID

    strTo = strChecker
    strCC = strMaker


    With OlMailItem
        .To = strTo
        .CC = strCC

P.S. - GetVisibleMaker is a function that retrieves the visible cells in a specific column after it has been filtered so that only the visible values are assigned to the variable.

 

[Norie]

To you want to set one email to multiple recipients?

 

[dougdrex]

To you want to set one email to multiple recipients?

Yes, generally each email contains one Employee ID in the "To" field and multiple values in the "Cc" field.

 

[dougdrex]

Any ideas on how to identify the Employee ID that Outlook is not recognizing with VBA code?

If necessary, I can post more of the code that I have so far.

 

[Norie]

I posted code that will show you which recipients aren't resolved.

For Each olRecpt In olEmail.Recipients
    If Not olRecpt.Resolved Then
        MsgBox olRecpt.Name
    End If
Next olRecpt

Obviously this uses a message box to show the unresolved names but it would be easy to adapt to output a list, or whatever.

 

[dougdrex]

I posted code that will show you which recipients aren't resolved.

For Each olRecpt In olEmail.Recipients
    If Not olRecpt.Resolved Then
        MsgBox olRecpt.Name
    End If
Next olRecpt

Obviously this uses a message box to show the unresolved names but it would be easy to adapt to output a list, or whatever.

A couple more questions...

How should the variables be defined/declared?
How can I pass the values in the current string variables to the ones mentioned in your code?

 

[Norie]

olEmail, or whatever the name of the variable you are using for the mail item, would be declared as Outlook.MailItem.

olRecpt, or whatever variable you want to use for the recipients, would be declared as Outlook.Recipient.

You wouldn't really pass the string variables to the code I posted.

What you would do is create the email and add all the recipients, i.e. the Employee IDs, to it and then ResolveAll on the Recipients collection of the email like this.

If olEmail.Recipients.ResolveAll Then
    MsgBox "All recipients resolved."
Else
    MsgBox "All recipients not resolved."

    ' find out which Employee IDs weren't resolved
    For Each olRecpt In olEmail.Recipients

        ' output name of unresolved recipient to the Immediate Window (CTRL+G)
        If Not olRecpt.Resolved Then
            Debug.Print olRecpt.Name
        End If

    Next olRecpt

    Exit Sub

End If

 

[dougdrex]

olEmail, or whatever the name of the variable you are using for the mail item, would be declared as Outlook.MailItem.

olRecpt, or whatever variable you want to use for the recipients, would be declared as Outlook.Recipient.

You wouldn't really pass the string variables to the code I posted.

What you would do is create the email and add all the recipients, i.e. the Employee IDs, to it and then ResolveAll on the Recipients collection of the email like this.

If olEmail.Recipients.ResolveAll Then
    MsgBox "All recipients resolved."
Else
    MsgBox "All recipients not resolved."

    ' find out which Employee IDs weren't resolved
    For Each olRecpt In olEmail.Recipients

        ' output name of unresolved recipient to the Immediate Window (CTRL+G)
        If Not olRecpt.Resolved Then
            Debug.Print olRecpt.Name
        End If

    Next olRecpt

    Exit Sub

End If

That makes sense. But how would the VBA code send the different values to the "To" and "Cc" fields?