Setting a "FROM" address in outlook automation code

[Chi1linVi1lain]

All,

I've been searching all morning for a way to set the "FROM" email address in a new email message in Outlook 2007 that's automated from Access 2007.

FYI - I'm writing this in a test DB so I can transfer the working code into a functional DB.

Here's the code I have:
(I placed a ' in the "Error" line to find where my code was failing so please ignore that)

Private Sub C1Email_Click()

'On Error GoTo Err_C1Email_DblClick


Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim EmailAddress As String
Set OutApp = CreateObject("Outlook.Application", "localhost")
Set OutMail = OutApp.CreateItem(olMailItem)
Forms(1).Command0.SetFocus
With OutMail

.From = "sales@finishing-technology.com"
.To = Me.TeamLeader.Column(1)
.CC = Me.AssignedTo
.BCC = ""
.Subject = "TEST"
.Body = "Testing 'From Email' address coding"
.Display 'Or .Send once happy with test results
​

End With

OutApp.Quit
Set OutMail = Nothing
Set OutApp = Nothing

Exit_C1Email_DblClick:
Exit Sub

Err_C1Email_DblClick:
MsgBox Err.Description
Resume Exit_C1Email_DblClick
End Sub
​

Any and all help would be greatly appreciated. THANKS!

Johnathon

 

[pbaldy]

Don't have my code in front of me, but I think you want .SentOnBehalfOfName instead of .From.

 

[Chi1linVi1lain]

I'd like to send an open report as an attachment to this email without saving it to the hard drive first.

I can send emails with attachments all day with the "Sendobject" functionality but that function doesn't allow the "From" field to be set... (Hence the above code) Now that I've set the "From" field, I'd like to insert the report as an attachment to the email. (The button that invokes the "Create Email Code" is actually on the Report in question so it's already open and the current window when this code is run.

 

[pbaldy]

This type of thing:

  Dim MyAttachments           As Variant
  Set MyAttachments = OutMail.Attachments
  MyAttachments.Add "PathToFile"

 

[Chi1linVi1lain]

Is there a way to attach the already open MS Access report to this email without saving the report to the hard drive first? I'm not going to need the report saved on the hard drive after the email is sent... and I'd really like to avoid having to write code to tell it where & how to save the report then code to delete the file once I'm done with it if at all possible...

 

[pbaldy]

To my knowledge, only with SendObject. You're caught between the rock and the hard place.