VBA Outlook (VBA Access 2000)

[roundboytx]

Trying to do something I thought was simple. I create a bunch of reports from access various formats Excel and PDF. Grab the file created Attach it to an email and send it. The problem I am having is they need to be sent from different Users. Attached is the code I have been using and it sends just fine but it doesn't change login profiles so it is sent as a different user. When it gets the 1st user it sends all the remaining files as that user. I need the abaility to send as diffrent user so any question that come go to the customer service person not me.

We are using an exchange server and this was the only method I could see to get the mail in the users Sent items and make it look like the User sent the Items. Am I thinking right or am I way off course.

Here's the code that doesn't change Profiles.

Dim odjOutlook As Object
Dim myNameSpace As NameSpace


Set dbCustomerEMailReports = dbreporting.OpenRecordset("tblEMailtoGo")

dbCustomerEMailReports.MoveFirst

Do Until dbCustomerEMailReports.EOF

txtProfile = Trim(dbCustomerEMailReports.sysdba_CONTACT_1_LASTN AME) & Left(dbCustomerEMailReports.sysdba_CONTACT_1_FIRST NAME, 1)

Dim odjOutlook As Object
Dim myNameSpace As NameSpace

'Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
Set myNameSpace = objOutlook.GetNamespace("MAPI")
myNameSpace.Logon txtProfile, , False, True

' Create the message.

Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
'Add the To recipient(s) to the message.
'Set objOutlookRecip = .Recipients.Add("George Cornelius")
Set objOutlookRecip = .Recipients.Add("<Larry@prfood.com>")
'Set objOutlookRecip = .Recipients.Add(dbCustomerEMailReports.PrCSMEMail)
' Set objOutlookRecip = .Recipients.Add(Trim(dbCustomerEMailReports.sysdba _CONTACT_FIRSTNAME) & " " & Trim(dbCustomerEMailReports.sysdba_CONTACT_LASTNAM E) & " <" & dbCustomerEMailReports.MfgEmail & ">")

' Set the Subject, Body, and Importance of the message.
.Subject = dbCustomerEMailReports.txtSubject
.Body = "Dear " & Trim(dbCustomerEMailReports.sysdba_CONTACT_FIRSTNA ME) & " " & Trim(dbCustomerEMailReports.sysdba_CONTACT_LASTNAM E) & "," & Chr(13) & Chr(13) & _
dbCustomerEMailReports.txtBodyText


' Add attachments to the message.
txtAttachmentname = dbCustomerEMailReports.strReportFileName
Set objOutlookAttach = .Attachments.Add(txtAttachmentname)

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
'myNameSpace.Logoff
End With

myNameSpace.Logoff
MsgBox myNameSpace.CurrentUser
objOutlook.Quit
Set objOutlookAttach = Nothing
Set objOutlookMsg = Nothing
Set myNameSpace = Nothing
Set objOutlook = Nothing
txtProfile = Null
dbCustomerEMailReports.MoveNext

Loop


Exit_Command0_Click:
Exit Sub

Help

Thanks

Larry
larry@prfood.com

[WendellB]

It seems like this should be simple, but unfortunately it doesn't seem to work that way. The selection of profile seems to somehow be set to a default when you login to start with, and it gets stuck there. I've seen at least one situation where you had to log off and log on again in order to get the profile to change. BTW, I assume you had Outlook closed as an application when you did your testing - if not it will gum things up as the start Outlook Application statement sees that it's already running with a given profile, and thats what you get.

[rory]

Hi Larry,
If you assign SendOnBehalf of permission (for each user you want to send as) to one account and then log in to Outlook as that account, you can use the createrecipient method to open the inboxes of each user in turn and send mail from there. If you do a search on createrecipient you should find some sample code (I'd suggest searching this forum and the Access forum as well.)
Hope that helps.

[roundboytx]

I already found the make sure outlook is not closed but logged off there seems to be a difference there but the weird part is it grabs the first user gets the profile and then sends the email as that user it just refuse the log that user off get then next then go from there. it always goes back to the first. Should work but doesn't. was hopeing you'd tell me to clear some obscure varible then it would work.

thanks

Larry