Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Oct 2001
    Location
    Texas, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Outlook (VBA Access 2000)

    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

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: VBA Outlook (VBA Access 2000)

    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.
    Wendell

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VBA Outlook (VBA Access 2000)

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    New Lounger
    Join Date
    Oct 2001
    Location
    Texas, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Outlook (VBA Access 2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •