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

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

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

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

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

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.

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  

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

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

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

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

Also, My OS is windows XP (not the professional version)

thanks

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

Thanks. I didn't think about that.....

Did you get the email from me?

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

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

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

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

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?

Hi baruchb1

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

Alan