Results 1 to 9 of 9

Excel email: Outlook not recognizing email ID

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,062

    Excel email: Outlook not recognizing email ID

    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
    Last edited by jomili; 04-16-2012 at 09:39 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1