Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2597
  • Last Modified:

using vba to change the default email account from an access application

Hi,
I am creating an access aplication that is supposed to retrieve emails form outlook parse them and send out responses. the application is supoosed to be able to handle MULTIPLE email account. right now the application can retreive emails from multiple account, howevert when it tries to reply, the response email always go from the default email account. This is not good since the recipients will be confused since they will not know the sender.

I was wondering if any of you ladies and gentlemen out there could tell me how to use VBA code so as to atomate the changing of the default email so that the respose alway leave FROM the correct email account. OR maybe there is a way just to instruct outlook what email account to send the email FROM, with out changing the default

Thank you kindly
Ron
0
ronstar101
Asked:
ronstar101
  • 11
  • 9
1 Solution
 
Alan WarrenApplications DeveloperCommented:

Hi Ron,

Try using CDO to send the mail responses.






Option Compare Database
Option Explicit
 

' References cdosys.dll - Microsoft CDO for Windows 2000 Library
' For XP systems Reference Microsoft CDO for exchange 2000 Library
' Reference: C:\Program Files\Common Files\Microsoft Shared\CDO\cdoex.dll
' http://www.developerfusion.com/show/3751/

' Usage:
' SendCDOSys "sender@some.com", "Ron@your.com", "test subject", "test body"


Public Sub SendCDOSys( _
  ByVal FromAddress As String, _
  ByVal ToAddress As String, _
  Optional ByVal Subject As String = "", _
  Optional ByVal TextBody As String = "", _
  Optional ByVal SMTPServer As String = "" _
)

  Dim oConfiguration As CDO.Configuration
  Dim oMessage As CDO.Message
 
  Set oConfiguration = New CDO.Configuration
  With oConfiguration.Fields
    .Item(cdoSMTPAuthenticate) = cdoAnonymous
    If Trim(SMTPServer) = "" Then
      .Item(cdoSendUsingMethod) = cdoSendUsingPickup
    Else
      .Item(cdoSendUsingMethod) = cdoSendUsingPort
      .Item(cdoSMTPServer) = SMTPServer
    End If
    ' iw: may need this in future, not researched
    '"SMTPLogonAccountName"   String. Read/write.
    '   The account name to use if your SMTP server requires you to log on.
    '"SMTPLogonPassword"      String. Write-only.
    '   The user password for the SMTP account. In order to protect passwords
    '   from detection, this field can be set but not retrieved.
    .Update
  End With
 
  ' create new message, add: from, to, subject and textbody
  Set oMessage = New CDO.Message
  With oMessage
    Set .Configuration = oConfiguration
    .From = FromAddress
    .To = ToAddress
    .Subject = Subject
    .TextBody = TextBody
    .Send
  End With

ExitProcedure:
  On Error Resume Next
  Set oMessage = Nothing
  Set oConfiguration = Nothing
  Exit Sub
End Sub



Regards Alan
0
 
Alan WarrenApplications DeveloperCommented:
Hi Ron,

the previous sample uses default smtp server installed with IIS and requires Windows XX Professional

this  should work using your ISP's smtp server as defined in your outlook email accounts delivery settings

' Test using:
' SendCDOSys "sender@some.com", "Recipient@some.com", "test subject", "test body","smtp.YourISP.com"


Option Compare Database
Option Explicit
 

' References cdosys.dll - Microsoft CDO for Windows 2000 Library
' For XP systems Reference Microsoft CDO for exchange 2000 Library
' Reference: C:\Program Files\Common Files\Microsoft Shared\CDO\cdoex.dll
' http://www.developerfusion.com/show/3751/

Public Sub SendCDOSys( _
  ByVal FromAddress As String, _
  ByVal ToAddress As String, _
  Optional ByVal Subject As String = "", _
  Optional ByVal TextBody As String = "", _
  Optional ByVal SMTPServer As String = "" _
)

  Dim oConfiguration As CDO.Configuration
  Dim oMessage As CDO.Message
 
  Set oConfiguration = New CDO.Configuration
  With oConfiguration.Fields
    .Item(cdoSMTPAuthenticate) = 1
    If Trim(SMTPServer) = "" Then
      .Item(cdoSendUsingMethod) = cdoSendUsingPickup
    Else
      .Item(cdoSendUsingMethod) = cdoSendUsingPort
      .Item(cdoSMTPServer) = SMTPServer
      .Item(cdoSendUserName) = "YourISP_AccountName"  ' <<< Your ISP Logon account name
      .Item(cdoSendPassword) = "YourISP_Password"  ' <<< Your ISP Logon password
    End If
    .Update
  End With
 
  ' create new message, add: from, to, subject and textbody
  Set oMessage = New CDO.Message
  With oMessage
    Set .Configuration = oConfiguration
    .From = FromAddress
    .To = ToAddress
    .Subject = Subject
    .TextBody = TextBody
    .Send
  End With

ExitProcedure:
  On Error Resume Next
  Set oMessage = Nothing
  Set oConfiguration = Nothing
  Exit Sub
End Sub




Alan
0
 
ronstar101Author Commented:
HI Alan,

thanks for the quick response, but as far as I understand this bit of code, send an email through an smtp server, which is great becasue some of the accounts that I am trying to make this software work if are on AOL, but some are on hotmail.... That said I need a bit of a walk through on how to use that code, and what it is good for. I am not an expret programmer (yet), and would greatly apreciate your help, or anybody elses.

thanks
Ron
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
ronstar101Author Commented:
Oh,
and right now I am sending emails through all those AOL and hotmail email VIA outlook. is this the wrong approach? please explain in detail. thank U
0
 
ronstar101Author Commented:
HI again,
I am using the 2nd solution ( the one that uses my ISP's smtp server, and  I think I am stating to understand this.
Basically, it uses my ISP server (in my case videotron.ca), to send the emails, but it masks them as if they are comming from a different email account.

if this is so, then great that will do the job...   The problem now is that I tried to use this bit of code and I get the follwing error:
Compile error:
                 User defined type not defined.

and this bit of code gets highlighted:    Dim oConfiguration As CDO.Configuration

what do I do to overcome this, please.

And please give me step by step instrutions.

Thank you once again.
0
 
Alan WarrenApplications DeveloperCommented:
Hi Ron,

If you are using a win2000 system you need to set a reference to Microsoft CDO for Windows 2000 Library.
In any code window > Tools > References > Microsoft CDO for Windows 2000 Library.

If you are using an XP system then you need to set a reference to Microsoft CDO for exchange 2000 Library
In any code window > Tools > References > Microsoft CDO for exchange 2000 Library.

Im using Access 2000 on an XP Pro system so I set a reference to Microsoft CDO for exchange 2000 Library which instantiates the cdoex.dll usually found in C:\Program Files\Common Files\Microsoft Shared\CDO

Collaborative Data Objects (CDO) encapsulates and exposes MAPI layers, making them easier to use.

Alan
0
 
ronstar101Author Commented:
HI Alan,
some thing is wrong. But before I go into that, I'd like that you for all your help so far.

Ok, so here goes, I thought I had it, and it seemed to work.... but none of the emails actually get anywhere.  no errors are happening, norton antivirus scaned the outgoing emails (I also tried running the code with the antivirus disabled), and the email seemed to be going out, but I was emailing them all to myself, and I am not getting any of these test email, nor are they bouncing back to the senders, which are also my accounts.

if there some configuriation I need to do with outlook? is this even going thorugh outlook?

I have totell you that htis code seems a bit wierd: since the emails aren't actually going through my email accounts, I can send emails with virtually any account. is something missing? is there supposed to be a line with the varios email accounts, and their individual passwords, or is the isp smtp username and password suficient (allong wiith the smtp outgoing server ofcourse).

Do you know what may be wrong and why the emails are just vanishing?

thanks  
0
 
Alan WarrenApplications DeveloperCommented:
Hi Ron,

This is not using your outlook application to send mail.

Try sending your isp's smtp server address in  the call to the sub


SendCDOSys "sender@some.com", "Ron@your.com", "test subject", "test body", "smtp.ozonline.com.au"    ' << your isp smtp server as defined in your account settings for outlook under servers > outgoing mail server.

Because your not providing one, the code will try send using pickup, if you have IIS installed, you will find the stalled mail in C:\Inetpub\mailroot\Pickup or one of the other folders in C:\Inetpub\mailroot\    (badmail, queue, drop, badmail)


Maybe post your code if you are still having problems, but ensure  to blank out your  password Ron




Alan
0
 
ronstar101Author Commented:
HI agian,

1.I don't have IIS installed, at least not as far as I know. do I need it? If so is it downloadable from some where.

2. I ran the code step by step, and it does not go into the piclkup, it skips the condition, and goes to the rihjt place.  . it gets the smtpServer, sUID (sender UIS), and the sPW (sender Pass word) as parametes that I added. all the data is being passed correctly.

3. I tried that 'call to the stub', but I don't really now what it means, and basically, I just uncommented the SendCDOSys line, the the i changed the nformation there, but asfar as I could tell nothing was hapening.

4.  how can I be sure that I am referencing everything I need to? Is it possible that I am not and that I am not getting an error?

5. following is the procedure you sent me (slightly altered in the parameter section) as it appears in my code

6. I don't have a directory C:/Inetpub

thanks again
==========================================================================
' Test using:
'SendCDOSys "sender@some.com", "Recipient@some.com", "test subject", "test body", "relais.videotron.ca"

'SendCDOSys ""sender@some.com", "Recipient@some.com", "test subject", "test body", "relais.videotron.ca"

***** Actual email addresses edited by *****
Alan Warren
Page Ed (Databases collective)
**********************************

' References cdosys.dll - Microsoft CDO for Windows 2000 Library
' For XP systems Reference Microsoft CDO for exchange 2000 Library
' Reference: C:\Program Files\Common Files\Microsoft Shared\CDO\cdoex.dll
' http://www.developerfusion.com/show/3751/

Public Sub SendCDOSys( _
  ByVal FromAddress As String, _
  ByVal ToAddress As String, _
  Optional ByVal Subject As String = "", _
  Optional ByVal TextBody As String = "", _
  Optional ByVal SMTPServer As String = "", _
  Optional ByVal sUID As String = "", _
  Optional ByVal sPW As String = "" _
)

  Dim oConfiguration As CDO.Configuration
  Dim oMessage As CDO.Message
 
  Set oConfiguration = New CDO.Configuration
  With oConfiguration.Fields
    .Item(cdoSMTPAuthenticate) = 1
    If Trim(SMTPServer) = "" Then
      .Item(cdoSendUsingMethod) = cdoSendUsingPickup
    Else
      .Item(cdoSendUsingMethod) = cdoSendUsingPort
      .Item(cdoSMTPServer) = SMTPServer
      .Item(cdoSendUserName) = sUID  ' <<< Your ISP Logon account name
      .Item(cdoSendPassword) = sPW  ' <<< Your ISP Logon password
    End If
    .Update
  End With
 
  ' create new message, add: from, to, subject and textbody
  Set oMessage = New CDO.Message
  With oMessage
    Set .Configuration = oConfiguration
    .From = FromAddress
    .To = ToAddress
    .Subject = Subject
    .TextBody = TextBody
    .Send
  End With

ExitProcedure:
  On Error Resume Next
  Set oMessage = Nothing
  Set oConfiguration = Nothing
  Exit Sub
End Sub
0
 
Alan WarrenApplications DeveloperCommented:
Hi Ron

I just sent a test message to ron_singer111 'at' hotmail.com, using the modified script  that you posted, let me know if you get it.

Alan
0
 
Alan WarrenApplications DeveloperCommented:
Ron

with your permission I will edit the email addresses that you posted in your last post. If we leave them as they are you will be buried under an avalanche of spam in the coming months - LOL

Alan
0
 
ronstar101Author Commented:
Also, My OS is windows XP (not the professional version)

thanks
0
 
Alan WarrenApplications DeveloperCommented:

Ron

I just sent myself three test messages using your script and the following syntax  to call the function

1... Sent with both Outlook and my IIS SMTP server running
2... Sent with IIS smtp server stopped
3... Sent with both Outlook closed and IIS smtp server stopped.

All came through no problems

SendCDOSys "sender@some.com", "Recipient@some.com", "Looking for ronstar101", "Is this ronstar101 from Experts Exchange?", "smtp.some.com.au", "MyAccountName", "MyPassword"


Alan
0
 
ronstar101Author Commented:
Thanks. I didn't think about that.....
0
 
Alan WarrenApplications DeveloperCommented:
Did you get the email from me?
0
 
ronstar101Author Commented:
I got it , it worked for you, but not for me. ...

Where am I supposed to put the

SendCDOSys "sender@some.com", "Recipient@some.com", "Looking for ronstar101", "Is this ronstar101 from Experts Exchange?", "smtp.some.com.au", "MyAccountName", "MyPassword"

in the code, am I suposed to just uncomment it, how do I call it , from where. In the sample you sent me, it seems like it is outside of the procedurte....
I don't understand how that works.

frustrating.

Thank you again
0
 
Alan WarrenApplications DeveloperCommented:
Your function should in a Module so it can be called from anywhere  in the DB

you can then put the call to invoke  the  function on the click event of any button

Private Sub SomeButton_Click()

SendCDOSys "sender@some.com", "Recipient@some.com", "Looking for ronstar101", "Is this ronstar101 from Experts Exchange?", "smtp.some.com.au", "MyAccountName", "MyPassword"

End Sub
0
 
ronstar101Author Commented:
ALAN, I AM A BONE HEAD!

all the mail was going into the junk mail folder, and I didn't realize it. it works just fine. thank you so much, and sorry for the blunderings.

Best regards
Ron Singer
montreal
0
 
Alan WarrenApplications DeveloperCommented:
hahahahaha

good one Ron!

Glad you got it sorted out, was starting to think it might be a hotmail thing.

Hey if you are interested in formatting your mail as HTML, have a look at the .HTMLBody property of the  oMessage object.
Accepts valid HTML as the body text...
And the .AddRelatedBodyPart method to include embedded images in the html body.


Alan



0
 
baruchb1Commented:
I have tried to use the routine above and I keep getting 'The transport failed to connect to the server' error. I double checked the SMTPServer String as well as my UID and passwords. They are all fine and I verified by connecting directly to the provider's email page using these UID and password. Any advice?
0
 
Alan WarrenApplications DeveloperCommented:
Hi baruchb1

does your ISP allow mail forwarding? some don't.

Alan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now