Bypassing Access warning messages

HAve you tried to run it using LateBinding instead and remove the refrence to CDO?

Code: 
Public Sub SendEMail(SendTo As String, eBody As String, eSubject As Variant)
Dim iCfg As Object
Dim iMsg As Object
Set iCfg = CreateObject("CDO.Configuration")
Set iMsg = CreateObject("CDO.Message")
 
With iCfg.Fields
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendusing[/URL]") = 2
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserverport[/URL]") = 25
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserver[/URL]") = "smtp.server.com"
     .Update
End With
 
With iMsg
    .Configuration = iCfg
    .Subject = eSubject
    .To = SendTo
    .HTMLbody = eBody
    .send
End With
Set iMsg = Nothing
Set iCfg = Nothing
End Sub

JR
 
JANR said:
HAve you tried to run it using LateBinding instead and remove the refrence to CDO?

Code: 
Public Sub SendEMail(SendTo As String, eBody As String, eSubject As Variant)
Dim iCfg As Object
Dim iMsg As Object
Set iCfg = CreateObject("CDO.Configuration")
Set iMsg = CreateObject("CDO.Message")
 
With iCfg.Fields
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendusing[/URL]") = 2
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserverport[/URL]") = 25
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserver[/URL]") = "smtp.server.com"
     .Update
End With
 
With iMsg
    .Configuration = iCfg
    .Subject = eSubject
    .To = SendTo
    .HTMLbody = eBody
    .send
End With
Set iMsg = Nothing
Set iCfg = Nothing
End Sub
JR
Click to expand...


How do you do that?
 
How do you do that?
Click to expand...

How do you latebind??

Just put this code in a standard module and run it.

Here is where I found it: http://www.ffdba.com/downloads/Send_E-Mail_With_CDO.htm

You call this function and pass along the parameters and it will send out the email.

Code: 
Private Sub Button_Click()
SendEMail "somemail@hotmail.com", "Sending a test mail using CDO", "Testing CDO"
End Sub

JR
 
Late binding seems awesome, I have implemented it and it works, but i don't quite understand from the code how passing parameters into the CDO function would not need the CDO 1.21 library. Can anyone clarify how this modification bypasses needing to actually reference the library?

Also, how am I able to add multiple email addresses to the TO or CC field in CDO? I can put up to 3 emails as TO, CC, BCC... but what If i just want 3 emails in the TO field? Does it automatically append strings or what?
 
to pass parameters to subs or function you have to define them like this:

Public Sub SomeName(Sender As String, Message As String)
...
...
End Sub

then elsewhere in yor application you pass this information to the Sub/Function.

SomeName "some text", "This is a message"


To use send to multiple rescipiants I suppose you'll have to use an array and loop to the array, but that is something I haven't done so you will have to look in access help for that.

JR
 
Ok something like this should send to multiple receivers:

Code: 
Option Compare Database
Option Explicit
 
Public Sub SendEMail(SendTo As String, eBody As String, eSubject As Variant)
Dim iCfg As Object
Dim iMsg As Object
Dim VarSender As Variant
 
Set iCfg = CreateObject("CDO.Configuration")
Set iMsg = CreateObject("CDO.Message")
 
With iCfg.Fields
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendusing[/URL]") = 2
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserverport[/URL]") = 25
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserver[/URL]") = "smtp.online.no"
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpauthenticate[/URL]") = 1
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendusername[/URL]") = "UserName"
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendpassword[/URL]") = "Password"
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendemailaddress[/URL]") = "Sender <[EMAIL="myEmail@hotmail.com"]myEmail@hotmail.com[/EMAIL]>"
    .Update
End With
 
If Len(eSubject & "") = 0 Then
    eSubject = " "
End If
 
For Each VarSender In GetStringArray(SendTo)
    With iMsg
        .Configuration = iCfg
        .Subject = eSubject
        .To = VarSender
        .HTMLBody = eBody
        .Send
    End With
Next
 
Set iMsg = Nothing
Set iCfg = Nothing
End Sub
 
Function GetStringArray(text As String) As String()
   GetStringArray = Split(text, ",")
End Function

The GetStringArray function splits the emailadresses and send them back to the sub in the For/Next loop. It uses a comma as the delimitor but you can use any delimitor you want.

Now you have to compile the SendTo string and separate the emailadresses with a comma. You can also use this to send to just one emailadress.

JR :)
 
JANR said:
Ok something like this should send to multiple receivers:

Code: 
Option Compare Database
Option Explicit
 
Public Sub SendEMail(SendTo As String, eBody As String, eSubject As Variant)
Dim iCfg As Object
Dim iMsg As Object
Dim VarSender As Variant
 
Set iCfg = CreateObject("CDO.Configuration")
Set iMsg = CreateObject("CDO.Message")
 
With iCfg.Fields
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendusing[/URL]") = 2
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserverport[/URL]") = 25
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserver[/URL]") = "smtp.online.no"
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpauthenticate[/URL]") = 1
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendusername[/URL]") = "UserName"
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendpassword[/URL]") = "Password"
    .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendemailaddress[/URL]") = "Sender <[EMAIL="myEmail@hotmail.com"]myEmail@hotmail.com[/EMAIL]>"
    .Update
End With
 
If Len(eSubject & "") = 0 Then
    eSubject = " "
End If
 
For Each VarSender In GetStringArray(SendTo)
    With iMsg
        .Configuration = iCfg
        .Subject = eSubject
        .To = VarSender
        .HTMLBody = eBody
        .Send
    End With
Next
 
Set iMsg = Nothing
Set iCfg = Nothing
End Sub
 
Function GetStringArray(text As String) As String()
   GetStringArray = Split(text, ",")
End Function
The GetStringArray function splits the emailadresses and send them back to the sub in the For/Next loop. It uses a comma as the delimitor but you can use any delimitor you want.

Now you have to compile the SendTo string and separate the emailadresses with a comma. You can also use this to send to just one emailadress.

JR :)
Click to expand...

Thanks JR! That worked like a charm!
 
Now a new problem I started running into was trying to used the CDO to non-company emails such as yahoo, gmail, etc.

When I type in a gmail address for auto email i get

Code: 
Run-time Error....
The server rejected one or more recipient addresses. The server response was: 550 5.7.1. Unable to relay for JohnDoe@gmail.com

Right now I have the username and password lines commented out as I don't have this information regarding the SMTP server. But it works sending emails to everyone in the same company domain.

Code: 
'    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username"
'    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"

Would i need this login to be able to send to external domains or is could i completely be using the wrong server to send emails from the get go??
 
Gmail uses Serverport 587 and not 25, you have to seperate gmail from regular email. One way would be to pass along the serverport variable from your call sub.

Code: 
Public Sub SendEMail(SendTo As String, eBody As String, eSubject As Variant, [COLOR=red]serverport as Integer[/COLOR])
 
....
...
With iCfg.Fields
    .Item("[URL="http://schemas.microsoft.com/cdo/configuration/sendusing"]http://schemas.microsoft.com/cdo/con...tion/sendusing[/URL]") = 2
    .Item("[URL="http://schemas.microsoft.com/cdo/configuration/smtpserverport"]http://schemas.microsoft.com/cdo/con...smtpserverport[/URL]") = [COLOR=red]serverport[/COLOR]    
    .Item("[URL]http://schemas.microsoft.com/cdo/con...ion/smtpserver[/URL]") = "smtp.online.no"
    .Item("[URL="http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"]http://schemas.microsoft.com/cdo/con...tpauthenticate[/URL]") = 1
    .Item("[URL="http://schemas.microsoft.com/cdo/configuration/sendusername"]http://schemas.microsoft.com/cdo/con...n/sendusername[/URL]") = "UserName"
    .Item("[URL="http://schemas.microsoft.com/cdo/configuration/sendpassword"]http://schemas.microsoft.com/cdo/con...n/sendpassword[/URL]") = "Password"
    .Item("[URL="http://schemas.microsoft.com/cdo/configuration/sendemailaddress"]http://schemas.microsoft.com/cdo/con...ndemailaddress[/URL]") = "Sender <[EMAIL="myEmail@hotmail.com"]myEmail@hotmail.com[/EMAIL]>"
    .Update
End With
 
.... rest of code

Now you have to figure out a good methode to seperate regular emails and those who uses Gmail.

JR :)
 
Last edited:
Just had a little test and you could try and see if it works for you to.

Try and send a regular email through serverport 587, if it works for you also then just change the serverport in your code.

JR :)
 
Nothing happens when i use port 587. My smtp server doesn't recognize these ports maybe?
 
Do you get the same error as before??

Right now I have the username and password lines commented out as I don't have this information regarding the SMTP server. But it works sending emails to everyone in the same company domain.
Click to expand...

I'm no expert but I think you will need to uncomment this. Some domains are probably rigid to prevent spammers, and Gmail is one of them I think.

In my test there was no problem using serverport 587, but then again I provide both a valid username and password.

JR:)
 
JANR said:
Do you get the same error as before??



I'm no expert but I think you will need to uncomment this. Some domains are probably rigid to prevent spammers, and Gmail is one of them I think.

In my test there was no problem using serverport 587, but then again I provide both a valid username and password.

JR:)
Click to expand...

After awhile, I finally got our network IT guy to setup a new account for me to use just for this database so I can enter in a username and password for the CDO.

However, when I go with the default port 25 I get the error:
The server rejected one or more recipient addresses.. 550.5.7.1 UNABLE TO RELAY FOR DOMAIN xxxx@gmail.com
Click to expand...
When I change the port to 587, I just get:
The transport failed to connect to server
Click to expand...
I'm reading this: http://www.msexchange.org/tutorials/Configuring-SMTP-Connector.html

Do I need to add a unique SMTP Connector to the Exchange Server Manager for every external domain ie. @gmail, @yahoo, @hotmail, @somebusiness.com, etc..???

NVM!!! I fixed it by realizing I had CDO's basic authentication commented out.... :eek:
 
Last edited:
You must log in or register to reply here.

Similar threads

Isaac
Google Voice Feature - Schedule Send
Replies
2
Views
600
GaP42
G
S
Error: The command or action 'SendObject' isn't available now.
Replies
9
Views
576
sonic8
S
D
Darshan Hiranandani : How to Open Outlook in HTML Format Using DoCmd.SendObject in Access
Replies
3
Views
611
arnelgp
arnelgp
D
Darshan Hiranandani : How to Open Outlook in HTML Format Instead of Plain Text via VBA in Access?
Replies
1
Views
609
Minty
Minty
LarryE
Solved Using ACCESS, WORD and OUTLOOK to send bulk E-Mails
2
Replies
38
Views
1,387
MajP
MajP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom