Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Aug 2005
    Posts
    23

    Unanswered: Send Email Using SMTP (Not Outlook)

    Hi,

    I have knowledge of how to send an email using the "DoCmd.SendObject" method.

    I am looking for an answer on how to send an email from Access by using an external smtp server such as "mail.abc.com" from sender "joe@abc.com". Has anyone done this? I am using Access 2003 and Access 2007.

    Any help is much appreciated.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I used a program called blat to send emails successfully from Access. It worked quite well.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, I have done that... without "blat". Struggling to remember where I found the original code though.

    Code:
    Public Sub VerySimpleSendMailWithCDOSample()
    
    Dim iCfg As Object
    Dim iMsg As Object
    
    Set iCfg = CreateObject("CDO.Configuration")
    Set iMsg = CreateObject("CDO.Message")
    
    With iCfg.Fields
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport")
    = 25
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
    "smtp.server.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")
    = 1
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") =
    "MyUserName"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") =
    "MyPassordNotAlwaysNeeded"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress")
    = "email@address.com.au"
    .Update
    End With
    
    With iMsg
    .Configuration = iCfg
    .Subject = "Subject"
    .To = "recipient.email@address.com.au"
    .TextBody = "MessageBody"
    .AddAttachment "FullPathToAttachment"
    .Send
    End With
    
    Set iMsg = Nothing
    Set iCfg = Nothing
    
    End Sub
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Aug 2005
    Posts
    23

    Thumbs up

    Thanks StarTrekker,

    I googled around and found code like this but it wasn't as clean as what you have showed here. So it left me a little

    This is real clear... Good stuff! Thanks!

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Hey, no problem
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Aug 2005
    Posts
    23
    I ran the script by StarTrekker and received the following error message:

    Run-time error '-2147220975 (8004021)':

    The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available.

  7. #7
    Join Date
    Aug 2005
    Posts
    23
    The smtp server is not on the machine that the code is being run. Is this why I am getting the error. I am trying to connect to a remote smtp server to send the mail.

  8. #8
    Join Date
    Sep 2006
    Posts
    265
    I push these type of emails out through each PC's SMTP using smart host and point to the smtp server required.

    Simon

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by bbsjoe
    The smtp server is not on the machine that the code is being run. Is this why I am getting the error. I am trying to connect to a remote smtp server to send the mail.
    No it's not why you are getting the error. You just need to specify all the right things and it works. I assume the SMTP server is online. You might have to specify a password for your SMTP server?

    It does work, I've used it a few times. You just have to substitute out the SMTP server login code.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Aug 2005
    Posts
    23

    Thumbs up

    For some reason I couldn't get the original code to work. I tried this instead and shut off smtp authentication:

    Public Sub SendEmailCDO(ByVal strTo As String, ByVal strMessage As String, ByVal strSubject As String, _
    Optional ByVal strAttach As String)

    Dim objEmail As Object

    On Error Resume Next

    Set objEmail = CreateObject("CDO.Message")

    'Stop

    objEmail.FROM = pubEmailSendAddress

    objEmail.To = strTo

    objEmail.Subject = strSubject

    objEmail.TextBody = strMessage

    If strAttach <> "" Then objEmail.AddAttachment strAttach

    objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2


    objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.abc.com"

    objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

    '_________________________________________________ ___
    '++Comment out Authentication++

    'objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

    'objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = pubEmailPassword

    '_________________________________________________ ___

    objEmail.Configuration.Fields.Update

    objEmail.Send

    If Err.Number <> 0 Then

    MsgBox "Error in sending. " & Err.Description

    Else

    MsgBox "Sent"

    End If

    Set objEmail = Nothing

    End Sub
    Everything seems to work now. Thanks Startrekker for the help.

  11. #11
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    For anyone else out there I have had great success using vbSendMail. Just google vbSendMail. Very easy to send emails from Access.

    C

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by bbsjoe
    For some reason I couldn't get the original code to work. I tried this instead and shut off smtp authentication:

    Everything seems to work now. Thanks Startrekker for the help.
    No problem -- happy to help
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Aug 2008
    Posts
    2
    I used the vbSendMail once with gmail smtp server. seems like this dll not support SSL so i cant login to gmail server. I use he StarTrekker code and seems ok with gmail.

    hope vbSendMail could support SSL in the future!!

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    For what it's worth, here's the code I use for blat:

    Public Sub SendEmail(ToVar As String, EmailAttch As String, PONumber As
    Variant)
    Dim FromVar, Server, FileToSend, OverrideBody, Subject As String FromVar = GetUser() & "@weccusa.org" Subject = "POReportEmail - " & PONumber OverrideBody = "Purchase Order Billed To Muni/Co-op" Server = "linux2.weccusa.org" FileToSend = "\\SQLServer\Databases\Databases\blatEmail\message .txt"

    Dim x As String

    'send email

    p_blat_location = "\\SQLServer\Databases\Databases\blatEmail\blat.ex e"

    'MsgBox (Attachment)
    'MsgBox EmailAttch

    x = p_blat_location & " " & FileToSend & " -s " & Chr(34) & Subject &
    Chr(34) & _
    " -t " & ToVar & " -f " & FromVar & " -server " & Server & " -attach " & EmailAttch

    'x = p_blat_location & " " & FileToSend & " -s " & Chr(34) & Subject &
    Chr(34) & _
    ' " -t " & ToVar & " -f " & FromVar & " -server " & Server & " -attach \\SQLServer\Databases\Databases\PurchaseOrders\POE mails\17725.snp"

    If Len(OverrideBody) > 0 Then
    x = x & " -body " & Chr(34) & OverrideBody & Chr(34)
    End If

    'debug
    x = x & " -debug -log c:\blat.log -timestamp"

    'MsgBox x
    'Debug.Print x

    'Shell x, vbHide

    Set WshShell = CreateObject("WScript.Shell")
    WshShell.Run "cmd.exe /c " & x, 0, True
    Set WshShell = Nothing

    End Sub
    Last edited by pkstormy; 08-29-08 at 04:24.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Oct 2009
    Posts
    2
    StarTrekker,

    your solution did the trick.

    Thanks people for all your help.


    Toptech

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •