Send Email From Access - VB Code to select Outlook 2007 Send Account

Scamquist
Scamquist used Ask the Experts™
on
I have code that will send email from an Access 2007 Table.  The code works great.  However, i have two accounts in Outlook 2007.  Is it possible to force the email to be sent from a specific account?
I have attached a my code.

Thank you in advance.

Steve


**** Added the MS Outlook Zone -- mbizup, Access Zone Advisor
Public Function LostBidEmail()
Dim objOutlook As New Outlook.Application
Dim ObjEmail As Outlook.MailItem
Dim strLtrContent As String
Dim rsContacts As New ADODB.Recordset

rsContacts.ActiveConnection = CurrentProject.Connection
		rsContacts.Open "tblLostBid"

'for each record in the tblEmails table, send an email
	Do While Not rsContacts.EOF

strLtrContent = "Thank you for bidding on " & rsContacts("Address") & ", however this assignment was awarded to another firm." & vbCrLf & vbCrLf


strLtrContent = strLtrContent & "Regards," & vbCrLf & vbCrLf
strLtrContent = strLtrContent & "Steve" & vbCrLf
    
'create an email regarding the testing of a new email
	Set ObjEmail = objOutlook.CreateItem(olMailItem)
' ObjEmail.Recipients.Add rsContacts("App_Email") & ", " & rsContacts("OrdByEmail") & ", " & rsContacts("Recipient1") & ", " &                	rsContacts("Recipient2") & ", " & rsContacts("Recipient3")
	ObjEmail.BCC = rsContacts("EmailAd")
	ObjEmail.Subject = "Appraisal Assignment" & "  " & rsContacts("Address")
	ObjEmail.Body = strLtrContent
'Send the message
    	ObjEmail.Display

        
    	rsContacts.MoveNext
Loop
End Function

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Nerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
I have a function that I posted in another question some time ago that does just that.

It uses CDO to set the "FROM" address.

One advantage of using CDO (which you will have to check as a reference) is that you do not get the Outlook security warning messages.

Without CDO, you can use ObjEmail.SentOnBehalfOfName
Function CDOSendEmail(varFrom As Variant, strTo As String, strSubj As String, strMess As String) 
    Dim strDefaultFrom  As String 
    Dim objMess As Object 
    Set objMess = CreateObject("CDO.Message") 
       
    ' This is your default FROM address, and to be used if varFrom is Null or Empty 
    strDefaultFrom = "DefaultAddress@MyEmail.com" 
     
    ' Subject 
    objMess.Subject = strSubj 
     
    ' Message 
    objMess.textbody = strMess 
     
    ' Determine if varFrom is Null/Empty and use default address, 
    ' otherwise use the From address passed through the function call 
    objMess.From = IIf(Nz(varFrom, "") = "", strDefaultFrom, varFrom) 
     
    ' To 
    objMess.To = strTo 
     
    ' This is a simple example... you can do a lot more 
     
    ' Send it 
    objMess.Send 
     
    ' Clear the object variable 
    Set objMess = Nothing 
     
End Function

Open in new window

Author

Commented:
i used the

ObjEmail.SentOnBehalfOfName = "myemail@email.com"

Worked well.

Author

Commented:
Thank you for the two options.  the ObjEmail.SentOnBehalfOfName
worked as I wanted it to.

Regards,

Steve
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Glad to help out :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial