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
Bookmarks