Email process SentOnBehalfOfName issue

[vickster3659]

Good morning,



I currently have an email process, with an attached report, that when the button is clicked, the email comes from that person who clicked the button. I would like to use the SentOnBehalfOfName to send the email from a specific mailbox, but I am getting an error message: Run-time error '91': Object variable or With block variable not set.

I am not sure what this error message is telling me, any help is appreciated!

Here is my code:

Private Sub cmdEmailToRequestors_Click()
Dim rs As DAO.Recordset
Dim myItem As Object
myPath = "\\DFS01\SHARED\CFA\VOL5\TREASURE\CASH_MGR\1- Treasury Operations\4- Database\Wire Tracking\"
stEmailMessage = "Attached is a report for disbursements released today per your request. If you would no longer like to receive this notification, please inform us and we will remove you from the distribution list accordingly. Best regards, Treasury Operations"
stSubject = "EFT Payments sent" & Format(Now(), " mm-dd-yyyy")
stReport = "rptEmailToRequestors"
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT RequestorID, RequestorEmail FROM qryEmailToRequestors WHERE [DateDue]=Date();")
While Not rs.EOF
myItem.SentOnBehalfOfName = "Wireroom@hanover.com"
DoCmd.OpenReport "rptEmailToRequestors", acViewPreview, , "RequestorID=" & rs!RequestorID & " AND [DateDue]=Date()"
DoCmd.SendObject acSendReport, , acFormatPDF, rs!RequestorEmail, , , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, , acFormatPDF, myPath & stSubject & ".doc", False, , acExportQualityPrint
DoCmd.Close acReport, stReport, acSaveNo
rs.MoveNext
Wend
MsgBox "All emails have been sent."
rs.Close
End Sub

Thanks,
Vickster3659

[pbaldy]

Well, your error is because you never set myItem. That said, you're mixing email methods which won't work. myItem would be used in Outlook automation, as would SentOnBehalfOfName:

http://support.microsoft.com/kb/161088

[vickster3659]

OK, it won't work. What would work? Sorry....the link classified this as Advanced, Expert, etc. I am certainly no expert, I reached out to this forum to help me build the process sans myItem. I searched this forum and saw someone that posted the myItem, figured I'd try it out.

Any assistance is greatly appreciated.
Vickster3659

[vickster3659]

Hello,
I've been hoping I can get some help on this, understand that the SentOnBehalfOfName won't work, but can anyone help me with what will work? My report process will send from the mailbox of whoever clicks the send button, I would like to have them sent from a specific mailbox.
Thanks,
Vickster3659

[pbaldy]

It should work, just not how you've done it. These are the relevant lines from a working db:

  Dim MyOutlook          As Object
  Dim MyMail             As Object
  Dim myattachments      As Variant

  Set MyOutlook = CreateObject("Outlook.Application")
  Set MyMail = MyOutlook.CreateItem(0)
  MyMail.To = rs!MaxOfEmail
  MyMail.SentOnBehalfOfName = "Blah@Whoever.com"
    
  MyMail.Subject = rs!CompanyName & " statement"

  Set myattachments = MyMail.Attachments
  myattachments.Add "c:\AccessAp\" & rs!CustomerAccount & "Statement.pdf"

  MyMail.Body = "Your statement is attached"
  MyMail.display

[vickster3659]

So....are you saying that the 'Set' procedure will not work with 'DoCmd.SendObject'? If this is true, I would rather not redo this whole procedure, I'd rather add what will work with the existing code.

[pbaldy]

No, they are separate methods. To my knowledge there's no way with SendObject to specify the sender. It uses the default email client.

[vickster3659]

sigh..... is there any hope of moving up from a beginner at this stuff?

.....I may be back......in need of help......

Thanks,
Vickster3659

[pbaldy]

We all start as beginners. You move up by diving in and beating it into submission.

[June7]

Review http://support.microsoft.com/kb/239568

This is a variation on the example given by Paul, doesn't use Outlook.

[vickster3659]

Thank you, June7. You were instrumental in getting my whole email process to finally work, I am afraid if I try to change anything, I will break what is working.

My process does in fact use Outlook. Everyone in my group has access to this one 'community' mailbox, this is the mailbox I would like to send on behalf of. To refresh your memory, my process attaches a filtered by date report, to send each recipient their own part of the report.

I am grateful to, and have learned so much from forums, but I am still a newbie, and probably will be for a long, long time! I am not a trained programmer, so much of what I see makes my eyes glaze over like a deer in the headlights haha! I may get brazen and try to get this to work at some point. I know where I can get help!

Thanks,
Vickster3659