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 :eek: :eek2:

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

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.

Attachment 74714

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

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.

Here's something I found on codeproject.

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

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 '"[email protected]"
        '.Item("urn:schemas:mailheader:disposition-notification-to") = strFrom '"[email protected]"

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