Excel VBA and Email

[Ironman]

Hello - This is my first post! Anyway, I've got a bit of VBA code that will send an email with an attachement to a contact list or distrubtion list. It works very well. However, there's always one of these aren't there, there are times when I'm off work and someone else needs to send out these emails. In an effort to have this person use the same VBA code I shared my Outlook Contact List with this person.

When the VBA code is run by this replacement person, it seems to work fine, meaning there are no errors. But...the email is never sent. I don't believe it goes to the Outbox either. So, I was hoping someone might have some thoughts/suggestions on using VBA code when the To: is a shared outlook contact list.

Below is a portion of the VBA code I use today:

On Error Resume Next
With OutMail
.To = "Daily NH" <<<< Here is the SHARED OUTLOOK CONTACT
.CC = ""
.BCC = ""
.Subject = "New Hire, Rehire, Term and Transfer Reports " & Format(Now(), "mm-dd-yy")
If DayNum = 2 Then
.Body = "Hello everyone. Happy Monday to you all! Attached are today's reports. Let me know if you have any questions." & vbNewLine & _
"Have a great day - Stay warm!!" & vbCrLf & vbCrLf & ""
ElseIf DayNum = 3 Then
.Body = "Good morning, Attached are Tuesday's reports, Have a good day!" & vbcrbl & vbCrLf & ""
ElseIf DayNum = 4 Then
.Body = "Hello all - Attached are Wednesday's reports, Have a good day!" & vbcrbl & vbCrLf & ""
ElseIf DayNum = 5 Then
.Body = "Good morning to you all, I've Attached Thursday's reports for you review, Have a good day!" & vbcrbl & vbCrLf & ""
ElseIf DayNum = 6 Then
.Body = "It's Friday! Attached you'll find todays reports. Have a good day and enjoy your weekend!" & vbcrbl & vbCrLf & ""
End If

'Attaching files
.Attachments.Add ("T:\HR Compensation\HRIS Files\Daily.Wkly NH Term Trans Rpt\2008\" & MMM & "\" & Format(Now(), "mm-dd-yy") & " NH, Rehire, Term" & ".pdf")
.Attachments.Add ("T:\HR Compensation\HRIS Files\Daily.Wkly NH Term Trans Rpt\2008\" & MMM & "\" & Format(Now(), "mm-dd-yy") & " Transfers" & ".pdf")
If Err.Number <> 0 Then
' error attaching detected
.Body = "Good morning...!" & vbCrLf & vbCrLf & _
"There is no new data to report, therefore there are no reports to send today." & _
vbCrLf & vbCrLf & "Have a great day!!" & vbCrLf & vbCrLf & ""
End If
.send 'or use .Display
End With

[lucas]

I would comment the on error resume next and see what happens.....it may be masking an error that would give you a clue.

[Ironman]

Thanks, I'll give that a try...

[Ironman]

OK, stuck in a meeting..! I tried the following:

1) commented the OnError lines
2) Added a line under With Outmail:
.sentonbehalfofName = myemail@work.com

3) Had a co-worker run the macro (remember it works fine when I run it cuz I think it's a contact list I created and shared to my co-worker)

Got an error message: system error &H80004005(-2147467259). unspecified error.

I did have her run it again the second time without the .sentonbehalfofname...produced the same error message.

Might you have suggestions/thoughts? Thanks for any ideas ...

[Cosmo]

Have you run it in debug mode to determine which line causes the error?