Solved

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

Posted on 2004-09-19
21
2,556 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

622 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