Excel email: Outlook not recognizing email ID

[jomili]

I'm not sure if this belongs in the Excel Programming area, or an Outlook group, but since it begins with Excel I'll start here.

The macro below picks up a report from the user's desktop and mails it to one of three people: Clarence, Keith, or "someone else". The someone else is where the problem lies ("Case is = 2") . The code works fine when hitting Yes to mail to Keith, or No to mail to Clarence; if I hit cancel, Excel puts up a message box asking for a valid email ID to mail to, so all that is just as designed. However, when I put a valid USERID in, even "henry.lastname@dshs.state.fl.us", which is used elsewhwere in the macro, I get a message back saying Outlook doesn't recognize one or more names. I'd appreciate some help in straightening this out.

Thanks,
John

Sub EmailFilled()

    Dim olApp As Object
    Dim olEmail As Object
    Dim Dte As String
    Dim datWEEK As Date
    Dim recip As String    'main recipient
    Dim recip2 As String    'the backup recipient
    Dim recip3 As String    'the CC person
    Dim strName As String    'some other poor sap
    Dim MyReturn


    Set olApp = CreateObject("Outlook.Application")
    Set olSent = olApp.Session.GetDefaultFolder(5)
    Set olInBox = olApp.Session.GetDefaultFolder(6)


    datWEEK = VBA.Format(Now, "MM-DD-YYYY")
    Do While VBA.WeekdayName(VBA.Weekday(datWEEK)) <> "Friday"
        datWEEK = datWEEK - 1
    Loop

    Set olEmail = olApp.CreateItem(0)

    MyReturn = MsgBox("Who do you want to QC this report?" & vbCrLf & vbCrLf & _
                      "To send to Keith, hit the YES button." & vbCrLf & _
                      "To send to Clarence, hit the NO button." & vbCrLf & _
                      "To send to someone else, hit Cancel.", _
                      vbInformation + vbYesNoCancel, "Who gets the Email?")

    Select Case MyReturn

    Case Is = 6    'Yes, let Keith do the dirty work
        recip = "henry.lastname@dshs.state.fl.us"
        recip2 = "Clarence.nobody@dshs.state.fl.us"
        recip3 = ""

    Case Is = 7    'No, send it to the backup
        recip = "Clarence.nobody@dshs.state.fl.us"
        recip2 = "henry.lastname@dshs.state.fl.us"
        recip3 = ""

    Case Is = 2    'send it to some other poor sap
        strName = InputBox(Prompt:="Enter an accurate email ID please.", _
                           Title:="ENTER THE DESIRED EMAIL ID", Default:="Email ID here")
        If strName = "" Then
            MsgBox "                Hey, you cancelled without entering an email ID!" & vbCrLf & vbCrLf & _
                   "Hit the ""Send"" button again once you decide what you want to do."
            Exit Sub
        End If
        
        If strName = "Email ID here" Then
            MsgBox "                   Hey, you didn't enter an email ID!" & vbCrLf & vbCrLf & _
                   "Hit the ""Send"" button again once you decide what you want to do."
            Exit Sub
        End If
        recip = strName
        recip2 = "henry.lastname@dshs.state.fl.us"
        recip3 = "Clarence.nobody@dshs.state.fl.us"
    End Select

    With olEmail
        .to = recip
        .cc = recip2 & recip3 & ";jomili.myself@dshs.state.fl.us"
        '.Subject = "This just a test-Please delete this email"
        .Subject = "QC-Filled to Authorized Comparison Report for the week ending " & Format(datWEEK, "mm-dd-yyyy")
        .Body = "Attached is the weekly Filled to Authorized Comparison Report." & vbCrLf & _
                "Please QC then forward on to Loris in QA Reporting." 
               
        .Attachments.Add ("C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\Regional CPS Filled Compared to Authorized.xls")
    
        .Send

    End With

    'olSent.Items(olSent.Items.Count).Copy.Move olInBox

    Set olApp = Nothing
    Set olEmail = Nothing
    Set olSent = Nothing
    Set olInBox = Nothing
    MsgBox "You probably didn't see anything, but Outlook just emailed your report." & vbCrLf & _
           "         Go ahead and check your Sent Items if you don't believe me."
End Sub

[JosephP]

maybe add

        .Recipients.resolveall

before the .Send line

[jomili]

Joseph,
I was very excited to get your post, because I was sure that was going to solve all my problems, but no such luck. The added code didn't seem to help at all. BTW, I noticed in my code posted above that I neglected to note that I have "Option Compare Text" at the top of the module.

[JosephP]

oh jeez - just saw the problem. there's a semicolon missing in the cc line when you have two extra addresses instead of one

.cc = recip2 & iif(len(recip3)> 0 ,";","") & recip3 & ";jomili.myself@dshs.state.fl.us"

[jomili]

JosephP, you're a genius (or at least your eyes are more open than mine)! The missing semi-colon was the problem. Thanks for spotting it, and for seeing what I kept looking past. I hope I can return the favor some day.

[JosephP]

definitely not a genius - I commented that line out when testing at first which is why I thought the resolveall had fixed it!

[jomili]

Well, you can have your own opinion of yourself, but in my world, right now, rank goes like this:

Jesus
My wife
JosephP
My mom
My kids
My friends
My boss
Everyone else

So, you're way up there!

[romperstomper]

you're a braver guy than me putting your wife at #2.

[JosephP]

and mom at #3!