We help IT Professionals succeed at work.

Access sends Outlook message from one account

Sasha42
Sasha42 used Ask the Experts™
on
I have a database on a network that users login to and fill out a form and Access sends an email via Outlook.  This works fine. The problem is that the email is being sent out of the Outlook account of the user that has logged into the database.  I have a specific person's Outlook account that I want all the email sent from.  What is the code that would set a "default"
Outlook account for all emails sent out of the database? Would the default person account always have to have Outlook open for it to work? The database will go in and read the reply emails and update the database.  Even better would be if the IT dept set up a generic Outlook account that is not assigned to anyone that the database can send from and read from. Is that possible?  Thanks for your thoughts.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Depends on the code you are using ... If using the sendobject method then this uses the default outlook account whereas a create object method can use a specific account.

Therefore either change the default account or use the outlook vba interface, and if you indicate what you do now we can work with you to resolve it.

Chris
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
For example and assuming 2007 or later in re outlook then:

Sub sender()
Dim olkApp As Object
Dim acct As Object

    Set olkApp = CreateObject("outlook.application")
    With olkApp.CreateItem(0)
        .Subject = "Diddly"
        .To = "fred@fred.com"
        .Body = "My MAil to yooHooo!"
        Set acct = sendAccount("mail@domain.com")
        If Not acct Is Nothing Then _
          .sendusingaccount = acct
        .Display ' replace with .send to send automatically
    End With
End Sub

Function sendAccount(strAccount As String) As Object
Dim olkApp As Object
Dim olkAccount As Object
Dim intIndex As Integer
Dim inspect As Inspector

    Set olkAccount = Nothing
    Set olkApp = CreateObject("outlook.application")
    For intIndex = 1 To olkApp.Session.Accounts.Count
        Set olkAccount = olkApp.Session.Accounts.Item(intIndex)
        If LCase(olkAccount.DisplayName) = LCase(strAccount) Then
            Set sendAccount = olkAccount
            Exit For
        End If
    Next
End Function

Open in new window


Chris

Author

Commented:
I am using the SendObject command but could use create object method can use a specific account.  I think I need to use the outlook vba interface you described.  I tried the code you posted above and it still sent email from my Outlook instead of another one that I tried. Thanks for your help.
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
The key requirement here is that the account details need to be in your outlook client ... you cannot and should not be able to access someone els's client.

Assuming you have the details stored in outlook then the right details will unlock the sending account ... as long as you are using 2007 or later.

Chris

Author

Commented:
Yes, I am using 2007.  Can you point me in the direction of seeing those details and how to use them.  This will be used wtih Exchange Server.
Software Quality Lead Engineer
Top Expert 2011
Commented:
The code provided picks it up ... have you used the correct account name?

With a quick tweak to the above code the following returns the current accounts available:

Function Accounts()
Dim olkApp As Object
Dim olkAccount As Object
Dim intIndex As Integer
Dim inspect As Inspector
Dim str As String

    Set olkAccount = Nothing
    Set olkApp = CreateObject("outlook.application")
    For intIndex = 1 To olkApp.Session.Accounts.Count
        Set olkAccount = olkApp.Session.Accounts.Item(intIndex)
        str = str & olkAccount.DisplayName & vbCrLf
    Next
    MsgBox str
    Debug.Print str
End Function

Open in new window


Chris

Author

Commented:
Chris, I don't quite understand the your first comment that has code.

Set acct = sendAccount("mail@domain.com")   'what should i put in here?  I only have one account with Outlook and I confirmed that with the Accounts function.  I would like to add the database administrators account and have all the email that is generated from the Access database be sent from that account.  Thanks again!
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
In order to send an email from a different address ... that address needs to be added as an account to your email ... and this is what is put in that line, I did say earlier "The key requirement here is that the account details need to be in your outlook client ... you cannot and should not be able to access someone els's client."

Oncde you add the DBMS admin mail account to your outlook it can be selected ... note you can set your account so it only sends mails so as not to upset any existing user for receipts to the account.

Chris

Author

Commented:
I have 10 "gatekeepers" that will be using the database for capital expenditure requests.  The gatekeepers enter in the requests then the database sends an email to first person in the approval routing list to approve it.  They reply to the email with a "A" approval or a "D" for denial in the subject line of the email.  The gatekeeper then opens the database clicks on a button that pulls the data out of the reply emails and updates the database and then sends an email to the next person in the approval routing list.  This all works. I was hoping to have all the emails that are sent and replied to handled by one Outlook account.  From what I think you are saying is that if all 10 of the gatekeepers add a DBMS admin mail account that it can be selected programmatically by the database and used to send out the email?
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
>>> From what I think you are saying is that if all 10 of the gatekeepers add a DBMS admin mail account that it can be selected programmatically by the database and used to send out the email?

Yes, and then each gatekeeper would use the code as above to select the account when sending the mail.

Chris