pauldonson
asked on
Send email from Access VBA using multiple email addresses on the same PC
Hi,
I have a client who has a number of companies and their marketing department need to send email out from 4 different domains.
I can happily use VBA to create an email that is sent using Outlook (I use redemption to do this)
Can anyone suggest how I might sent an email using a different account depending on what the user has selected?
I have a client who has a number of companies and their marketing department need to send email out from 4 different domains.
I can happily use VBA to create an email that is sent using Outlook (I use redemption to do this)
Can anyone suggest how I might sent an email using a different account depending on what the user has selected?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To clarify,
You want the "From" adressee to be different for each mailing? Or you want more than one "TO" addressed to appear on each e-mail?
J
You want the "From" adressee to be different for each mailing? Or you want more than one "TO" addressed to appear on each e-mail?
J
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
To clarify it is the "From " (and therefore the reply address) that needs to be different in each instance.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
rockiroads:
That works fine! Thanks for that - TBH I haven't a clue what it is doing but I like it! Bit worried about the references to scemas.microsoft.com - what are they all about?
That works fine! Thanks for that - TBH I haven't a clue what it is doing but I like it! Bit worried about the references to scemas.microsoft.com - what are they all about?
ASKER
Rockiroads: My current code using redemption and Outlook allows me to save a copy of the email for it to be imported into my SQL database. Is there a way of adapting your function to do this but Access comes up with an error if I insert the line objEmail.SaveAs ("C:\Test.msg")
Cool
CDO is a Microsoft thing and those links seem to be reading up/setting up some parameters for CDO. I had a look briefly but couldnt come up with much.
I use this and normally give this out as my example mostly along with Outlook Automation.
CDO does not go via outlook, instead goes straight to server.
Note this though
http://www.microsoft.com/downloads/details.aspx?FamilyID=2714320d-c997-4de1-986f-24f081725d36&displaylang=en
CDO is a Microsoft thing and those links seem to be reading up/setting up some parameters for CDO. I had a look briefly but couldnt come up with much.
I use this and normally give this out as my example mostly along with Outlook Automation.
CDO does not go via outlook, instead goes straight to server.
Note this though
http://www.microsoft.com/downloads/details.aspx?FamilyID=2714320d-c997-4de1-986f-24f081725d36&displaylang=en
ok, I didnt know u wanted to save the message as well
I dont believe you can save messages as a message file
What is it you need to store, could u not write directly to the database given the info u got?
or do u have some process that reads the outlook message files and inserts into db
I dont believe you can save messages as a message file
What is it you need to store, could u not write directly to the database given the info u got?
or do u have some process that reads the outlook message files and inserts into db
ASKER
I currently construct the email and save it on the hard disk as an MSG file and then use ADODB.Stream to send it to the SQL database, this then neatly keeps it in the format in which it was sent together with attachments.
I have had a look at the FAQ on the Redemption site as suggested by LMConsulting but I can't make head nor tail of it!
I have had a look at the FAQ on the Redemption site as suggested by LMConsulting but I can't make head nor tail of it!
Looking at Redemption
there seems to be this that u could possibly use
SafeMailItem.Sender.Addres s
or
SafeMailItem.Fields(PR_SEN DER_EMAIL_ ADDRESS).
or
SafeMailItem.SenderEmailAd dress
there seems to be this that u could possibly use
SafeMailItem.Sender.Addres
or
SafeMailItem.Fields(PR_SEN
or
SafeMailItem.SenderEmailAd
I dont know if this is readonly property or perhaps u can set them
Regarding CDO
I found this
http://64.233.183.104/search?q=cache:9SV_py00QYUJ:msdn.microsoft.com/library/en-us/cdosys/html/_cdosys_ibodypart_getdecodedcontentstream.asp+cdo+adostream&hl=en&gl=uk&ct=clnk&cd=1
Dont know if it will help
I found this
http://64.233.183.104/search?q=cache:9SV_py00QYUJ:msdn.microsoft.com/library/en-us/cdosys/html/_cdosys_ibodypart_getdecodedcontentstream.asp+cdo+adostream&hl=en&gl=uk&ct=clnk&cd=1
Dont know if it will help
ASKER
OK. I got excited then!
The first one failed, the second one was ignored and the third one is read only.
I have tried the solution in the Redemption FAQ and the mail always bounces back as undeliverable (I presume our server won't authorise the "spoofed" email address I am using)
Why do these seemingly simple problems take forever to sort out???!!!???
The first one failed, the second one was ignored and the third one is read only.
I have tried the solution in the Redemption FAQ and the mail always bounces back as undeliverable (I presume our server won't authorise the "spoofed" email address I am using)
Why do these seemingly simple problems take forever to sort out???!!!???
ASKER
Right, got that working now and it saves it.
This is where I am at:
Dim objEmail As Object
On Error Resume Next
Set objEmail = CreateObject("CDO.Message" )
'**** SET EMAIL ADDRESS OF SENDER HERE
objEmail.From = "spoofed@yahoo.co.uk"
objEmail.To = "spoof@Spoof.co.uk"
objEmail.subject = "Spoof Test"
objEmail.TextBody = "Spoof test should be from Yahoo"
'If strAttach <> "" Then objEmail.AddAttachment strAttach
objEmail.Configuration.Fie lds.item(" http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'**** HERE YOU ENTER THE SMTP SERVER e.g. smtp.xxx.com
objEmail.Configuration.Fie lds.item(" http://schemas.microsoft.com/cdo/configuration/smtpserver") = "MAILSERVER"
objEmail.Configuration.Fie lds.item(" http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fie lds.Update
objEmail.getstream.SaveToF ile "C:\Test.eml", adSaveCreateOverWrite
objEmail.Send
If Err.Number <> 0 Then
MsgBox "Error in sending. " & Err.Description
Else
MsgBox "Sent" 'remove this if u dont want confirmation
End If
Set objEmail = Nothing
My next issue is I need it in MSG format (so it can be opened with Outlook) - eml will only open with Outlook Express which none of my clients have configured.
If I open the eml file with Outlook it creates a new email message with the eml as an attachment. If I save it as an MSG it won't open it.
Grrr! I think we might be close now, I will have to catch up with you tomorrow, about to be chucked out of the building now!
This is where I am at:
Dim objEmail As Object
On Error Resume Next
Set objEmail = CreateObject("CDO.Message"
'**** SET EMAIL ADDRESS OF SENDER HERE
objEmail.From = "spoofed@yahoo.co.uk"
objEmail.To = "spoof@Spoof.co.uk"
objEmail.subject = "Spoof Test"
objEmail.TextBody = "Spoof test should be from Yahoo"
'If strAttach <> "" Then objEmail.AddAttachment strAttach
objEmail.Configuration.Fie
'**** HERE YOU ENTER THE SMTP SERVER e.g. smtp.xxx.com
objEmail.Configuration.Fie
objEmail.Configuration.Fie
objEmail.Configuration.Fie
objEmail.getstream.SaveToF
objEmail.Send
If Err.Number <> 0 Then
MsgBox "Error in sending. " & Err.Description
Else
MsgBox "Sent" 'remove this if u dont want confirmation
End If
Set objEmail = Nothing
My next issue is I need it in MSG format (so it can be opened with Outlook) - eml will only open with Outlook Express which none of my clients have configured.
If I open the eml file with Outlook it creates a new email message with the eml as an attachment. If I save it as an MSG it won't open it.
Grrr! I think we might be close now, I will have to catch up with you tomorrow, about to be chucked out of the building now!
ASKER
Is there a way of using Outlook Accounts to send the emails? The PCs are set up with multiple accounts anyway and if I could simply choose the correct one then problem solved.
There must be a simple eml to msg conversion code/tool out there.
e.g. http://www.outlookextract.com/ - this u have to pay for but it does mean that there is stuff out there
Sorry the CDO thing didnt work out then
This code uses Outlook Automation, Im afraid though, you may get the security warning though, but it does use Outlook
I was unable to find anything regarding setting Sender details
Public Function SendRRMailEE(ByVal sReceipient, _
ByVal sSubject As String, _
ByVal sBodyText As String, _
ByVal sAttachment As String) As Boolean
Dim objOutlook As Object
Dim objEmailMessage As Object
'Specify error handler
On Error GoTo SMError
'If no receipient passed then exit with error
If Trim$(sReceipient) = "" Then
MsgBox "No Receipient name has been specified", vbExclamation, "Send Mail"
Exit Function
End If
'Create outlook objects
'note, you need to add the Outlook reference in Modules Menu option Tools/References
Set objOutlook = CreateObject("Outlook.Appl ication")
Set objEmailMessage = objOutlook.CreateItem(0)
'Set subject if specified
If sSubject <> "" Then objEmailMessage.Subject = sSubject else objEmailMessage.Subject = "Automated Email"
'If message body specified then add that
objEmailMessage.Body = sBodyText
objEmailMessage.ReadReceip tRequested = True
objEmailMessage.Originator DeliveryRe portReques ted = True
'If attachment passed then add that
If sAttachment <> "" Then objEmailMessage.Attachment s.Add sAttachment
'Set the email object
objEmailMessage.Recipients .Add sReceipient
'Resolve email address
objEmailMessage.Recipients .ResolveAl l
'Display email - diagnostic purposes only - otherwise comment out
objEmailMessage.Display
objEmailMessage.send
'MsgBox "Message has been successfully sent", vbInformation, "Send Mail"
'Return success
SendRRMailEE = True
GoTo SMDone
SMError:
'Return failure and display error message
SendRRMailEE = False
MsgBox "An error occurred when trying to send the email." & vbCrLf & vbCrLf & Err.Description, vbCritical, "Send Mail"
SMDone:
'Clear down the objects created
On Error Resume Next
Set objEmailMessage = Nothing
Set objOutlook = Nothing
End Function
e.g. http://www.outlookextract.com/ - this u have to pay for but it does mean that there is stuff out there
Sorry the CDO thing didnt work out then
This code uses Outlook Automation, Im afraid though, you may get the security warning though, but it does use Outlook
I was unable to find anything regarding setting Sender details
Public Function SendRRMailEE(ByVal sReceipient, _
ByVal sSubject As String, _
ByVal sBodyText As String, _
ByVal sAttachment As String) As Boolean
Dim objOutlook As Object
Dim objEmailMessage As Object
'Specify error handler
On Error GoTo SMError
'If no receipient passed then exit with error
If Trim$(sReceipient) = "" Then
MsgBox "No Receipient name has been specified", vbExclamation, "Send Mail"
Exit Function
End If
'Create outlook objects
'note, you need to add the Outlook reference in Modules Menu option Tools/References
Set objOutlook = CreateObject("Outlook.Appl
Set objEmailMessage = objOutlook.CreateItem(0)
'Set subject if specified
If sSubject <> "" Then objEmailMessage.Subject = sSubject else objEmailMessage.Subject = "Automated Email"
'If message body specified then add that
objEmailMessage.Body = sBodyText
objEmailMessage.ReadReceip
objEmailMessage.Originator
'If attachment passed then add that
If sAttachment <> "" Then objEmailMessage.Attachment
'Set the email object
objEmailMessage.Recipients
'Resolve email address
objEmailMessage.Recipients
'Display email - diagnostic purposes only - otherwise comment out
objEmailMessage.Display
objEmailMessage.send
'MsgBox "Message has been successfully sent", vbInformation, "Send Mail"
'Return success
SendRRMailEE = True
GoTo SMDone
SMError:
'Return failure and display error message
SendRRMailEE = False
MsgBox "An error occurred when trying to send the email." & vbCrLf & vbCrLf & Err.Description, vbCritical, "Send Mail"
SMDone:
'Clear down the objects created
On Error Resume Next
Set objEmailMessage = Nothing
Set objOutlook = Nothing
End Function
or use a different STMP server?
If so, have u looked into using CDO?