Access 2010 does not send the email only in a few machines the others works fine. 1

[robles74]

Hi everyone.

I have a mystery to solve, I wrote a VBA sub to send an Email (From Access 2010).

The macro works fine in 9 computers but it fails to do his work in just 2 computers, it is not a big deal but I'll be wonder if I can fix this issue, here is the code, any help will be REALLY appreciated.

Thanks in advance and best regards.

Sub SendEmail_Pull_and_Send_Fields_Alt1()
Dim oOutlook As Object
Dim oEmailItem As Object
ActDte = Date
'Prevent 429 error, if Outlook not open
On Error Resume Next
Err.Clear
Set oOutlook = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
Set oOutlook = Outlook.Application
End If
Set oEmailItem = oOutlook.createitem(olmailitem)
With oEmailItem
.To = ""
.cc = ""
.subject = "Pull and send fields updated in TRICareTST4 database on " & ActDte
.body = "Pull and send fields for (Repace with your data) has been updated." & vbNewLine & "Please continue with the assigned flow."
.display
End With
Set oEmailItem = Nothing
Set oOutlook = Nothing
End Sub

 

[SkipVought]

Hi,

I'd use the CDO Object rather than Outlook.

thread222-1143694

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

And as a side note, I would not do this:
[tt]On Error Resume Next
[/tt]
which pretty much says: whatever happens, ignore it, I don't want to know about it, just keep going.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[robles74]

Hi guys.

Thanks a lot for your replies, but I can't do the code to work I try doing some mods to the code in thread222-1143694: sending email through Vb, but I have the "connection error" I have this. Please "slap" me and then forgive me if (surely) I'm doing something wrong.

Option Explicit
 
Private Sub Command1_Click()
    With New CDO.Message
        With .Configuration.Fields
            .Item(cdoSMTPAuthenticate) = cdoBasic
            .Item(cdoSendUserName) = "myusername"
            .Item(cdoSendPassword) = "mypassword"
            .Item(cdoSendUsingMethod) = cdoSendUsingPort
            .Item(cdoSMTPServer) = "I replace with my SMTP server"
            .Item(cdoSMTPConnectionTimeout) = 10
            .Item(cdoSMTPServerPort) = 25  ' I try also with the 587 and 465 port,  I try to verify if the ports are blocked but how it is a corporate environment I can't use the Telnet command 
            .Update
        End With
        .To = "luis.roblesamador@bla,bla.com"
        .Subject = "Example"
        .TextBody = "Hello"
        .From = "luis.roblesamador@bla,bla.com"
        .Send
    End With
End Sub

 

[Andrzejek]

Do you have reference to Microsoft CDO for Windows 2000 Library?

I have just these Configuration Fields and it works (no username or password, etc.)

...
With .Configuration.Fields
    .Item(CDO.cdoSMTPServer) = "I replace with my SMTP server"
    .Item(CDO.cdoSMTPServerPort) = 25
    .Item(CDO.cdoSendUsingMethod) = CDO.cdoSendUsingPort
    .Item(cdoSMTPConnectionTimeout) = 10
    .Update
End With
...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[robles74]

Greaaaaat!!!!!, you're the man, it works.

And yes I already has the Microsoft CDO for Windows 2000 reference marked.

I just remove the username and password and work, I really don't know why, but works.

Thanks a lot.

How can I mark this treas as SOLVED??

 

[strongm]

>I just remove the username and password and work, I really don't know why, but works.

SMTP gateways are not always configured to require security to connect (indeed, SMTP itself does not include any provision for usernames and passwords; these are part of the Authentication extension to SMTP which, although common, is not mandatory on Mail Transfer Agents (MTAs)).


So, Andrzejek's solution will work fine for you until:

a) MTA admins decide they need to impose secure connection, thus requiring credentials
b) Admins realise they didn't mean to configure MTA as an open relay host ...

>I try also with the 587 and 465 port, I try to verify if the ports are blocked but how it is a corporate environment I can't use the Telnet command

If you've got Powershell I have a script to verify ports without needing telnet, and avoiding ping, which is often disabled on enterprise networks:

[blue]param([string]$testhost="127.0.0.1", [Int32]$testport=137)
try {
	$tcp=new-object system.net.sockets.tcpclient
	$tcp.connect($testhost, $testport) 
	$tcp.close | Out-Null
	"All OK testing host " + $testhost + " on port " + $testport
}
catch {
	"Error testing host " + $testhost + " on port " + $testport
}
[/blue]

(actually, if you have W8.1 or later, then there is a single Powershell command, Test-NetConnection that is considerably more flexible and powerful than the above script)

 

[Andrzejek]

>How can I mark this treas as SOLVED??
Just give stars to whoever contributed to the solution of your problem (like you did to my post, thank you ) and consider this tread solved and closed.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.