Solved

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

Posted on 2004-09-19
21
2,527 Views
Last Modified: 2010-01-27
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
Comment
Question by:ronstar101
  • 11
  • 9
21 Comments
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12098780

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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12098935
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
 

Author Comment

by:ronstar101
ID: 12118475
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
 

Author Comment

by:ronstar101
ID: 12118489
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
 

Author Comment

by:ronstar101
ID: 12118787
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
 
LVL 26

Accepted Solution

by:
Alan Warren earned 300 total points
ID: 12119196
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
 

Author Comment

by:ronstar101
ID: 12120217
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12120322
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
 

Author Comment

by:ronstar101
ID: 12123883
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12124174
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 26

Expert Comment

by:Alan Warren
ID: 12124224
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
 

Author Comment

by:ronstar101
ID: 12124393
Also, My OS is windows XP (not the professional version)

thanks
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12124401

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
 

Author Comment

by:ronstar101
ID: 12124414
Thanks. I didn't think about that.....
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12124419
Did you get the email from me?
0
 

Author Comment

by:ronstar101
ID: 12124480
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12124539
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
 

Author Comment

by:ronstar101
ID: 12124557
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12124688
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
 

Expert Comment

by:baruchb1
ID: 12351996
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12354551
Hi baruchb1

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

Alan
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now