Results 1 to 6 of 6

Thread: VBA to SMTP - best route and links pls

  1. #1

    Thread Starter
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    VBA to SMTP - best route and links pls

    Hi,

    My workplace have dropped a bombshell and done a runner.

    They are swapping from MS Exchange to Google GMail. Not too much of a problem.

    Well there was a niggle of a problem. Some of the VBA coding I look after (in Excel and Access) generates emails. Auto reports and sends with attachments. I thought this might be a problem, but shelved it.

    Wish I'd asked someone now cause its going live next week

    I thought that MS Outlook could possibly be tied to gmail, a back up plan. This got shot down in the last 30 mins cause the outlook is locked down and I wont be able to get it to talk to GMail (Imap disabled - security policy or something).

    The people in the know say they cannot give more bandwidth (uh?) and I should look at using SMTP. They can provide a server address.



    So, finally - my question and a request.

    qs: Can VBA talk with SMTP server?

    Can you provide any links to read up on how to do this including attachments?
    If it need API I would need the calls and I will go n investigate.

    Thanks in advance

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  2. #2
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: VBA to SMTP - best route and links pls

    Yes it can, i have done this before. In fact i have an Example VB project somewhere if i can dig it out .... note this is not my project but the example one i used when creating my own SMTP Mailer at the last place i worked.

    SMTP Mail Test.rar
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  3. #3

    Thread Starter
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: VBA to SMTP - best route and links pls

    Thanks for the Rar, will look at home. Any good links online I can look at or should I msdn search?

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  4. #4
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: VBA to SMTP - best route and links pls

    To be honest i cant remember exactly were i got the code from, it was a while ago however it works great.

    There is a dll oSMTP which is included in the project which does all the heavy lifting for you which make it rather easy.
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  5. #5
    Fanatic Member dmaruca's Avatar
    Join Date
    May 2006
    Location
    Jacksonville, FL
    Posts
    577

    Re: VBA to SMTP - best route and links pls

    Here's something I found on codeproject.

    http://www.codeproject.com/KB/COM/smtp.aspx

  6. #6

    Thread Starter
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: VBA to SMTP - best route and links pls

    After a bit of a search I am now hitting the brick wall of fog. Can't see what I 've done wrong...

    I'm trying to connect to the gmail account I have (rather than the works one which isn't quite set up yet).


    The transport failed to connect to the server

    My Code
    Code:
    Public Function EmailCDO(ByVal strFrom As String, ByVal strTo As String, ByVal strCC As String, ByVal strBCC As String _
                        , ByVal strSubject As String, ByVal strBody As String, ByVal strAttach As String _
                        , ByVal blnShowErr As Boolean, ByVal blnShowMsgs As Boolean) As Boolean
    
    'search msdn for cdo.configuration
    'http://support.microsoft.com/kb/286431
    'http://msdn.microsoft.com/en-us/library/ms526318(EXCHG.10).aspx
    'http://www.rondebruin.nl/cdo.htm
    'http://www.lewisroberts.com/2006/06/09/sending-cdomessage-with-importance/
    
    ' Send by connecting to port 25 of the SMTP server.
        Dim iMsg As Object
        Dim iConf As Object
        Dim Flds As Object
        Dim strCDO As String
        
        Const cdoSendUsingPort = 2
        EmailCDO = False
        
        Set iMsg = CreateObject("CDO.Message")
        Set iConf = CreateObject("CDO.Configuration")
        
        Set Flds = iConf.Fields
        
    
    ' Set the CDOSYS configuration fields to use port 25 on the SMTP server.
        
        strCDO = "http://schemas.microsoft.com/cdo/configuration/"
        
        With Flds
            .Item(strCDO & "sendusing") = cdoSendUsingPort
    'ToDo: Enter name or IP address of remote SMTP server.
            .Item(strCDO & "smtpserver") = "smtp.gmail.com" 'require the server
            .Item(strCDO & "smtpconnectiontimeout") = 10
            .Item(strCDO & "smtpserverport") = 25 '587 '
            
            
    '---- security bits
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "user"
            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "pwd"
    
    
    ' Update fields
            .Update
        End With
        
        
    '    Set Flds = iMsg.Fields
        
    '    With Flds
        ' Set importance or Priority to high
            '.Item("urn:schemas:httpmail:importance") = 2 '0 low, 1 normal, 2 high - outlook express
            '.Item("urn:schemas:mailheader:X-Priority") = 1 'outlook 2003 as well
            '.Item("urn:schemas:mailheader:X-MSMail-Priority") = "High" 'outlook 2003
            
    ' Request read receipt
            '.Item("urn:schemas:mailheader:return-receipt-to") = strFrom '"ron@debruin.nl"
            '.Item("urn:schemas:mailheader:disposition-notification-to") = strFrom '"ron@debruin.nl"
    
    ' Update fields
    '        .Update
    '    End With
        
        
        ' Apply the settings to the message.
        With iMsg
            Set .Configuration = iConf
            .To = strTo '"<email address>" 'ToDo: Enter a valid email address.
            If Len(strCC) > 0 Then .cc = strCC
            If Len(strBCC) > 0 Then .cc = strBCC
            .From = strFrom '"<email address>" 'ToDo: Enter a valid email address.
            .Subject = strSubject '"This is a test CDOSYS message
            .HTMLBody = strBody 'strHTML
            
    '---- attachments...
            If Len(strAttach) > 0 Then
            
            End If
            
            On Error Resume Next
            .Send
            If Err.Number <> 0 Then
                If blnShowMsgs Then MsgBox "Failed to send email" & vbCrLf & Err.Number & " - " & Err.Description, vbOKOnly + vbInformation
                Err.Clear
            Else
                If blnShowMsgs Then MsgBox "Sent", vbOKOnly + vbInformation
                EmailCDO = True
            End If
            On Error GoTo 0
        End With
        
        Set Flds = Nothing
        Set iMsg = Nothing
        Set iConf = Nothing
        
    End Function
    Anything wrong there?

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

Posting Permissions

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



Click Here to Expand Forum to Full Width