Attach worksheet to gmail through VBA

[hawley]

I have a macro that will copy a worksheet and then attach that new workbook to an email through lotus notes. Our company is moving to Google Mail (gmail) and I am trying to find away to continue to do this. Google Mail does not appear in the Internet Properties -> Programs -> Email where I need it the Email Application to appear for the code to work. I have done some searching but am not having no luck so was hoping someone had some ideas, suggestions or knew how to do this. Any thing is appreciated.

Here is the code I currently use.

    ChDir "C:\Excel"
    ActiveWorkbook.SaveAs FileName:= _
        "C:\Excel\" & ThisWorkbook.ActiveSheet.Name & " PERFORMANCE SUMMARY.xls" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    Application.Dialogs(xlDialogSendMail).Show
    newvendor.Activate
    ActiveWindow.Close
    Vendor.Activate

 

[hawley]

I was able to find an answer. Hopefully this will help others.

Private Sub btnSubmit_Click()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim NewFile As String
Dim Flds As Variant
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    'copy this current workbook and place in another workbook values and format only.
    'that new copy will be the attachement.
    Call Create_File

    iConf.Load -1
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "our work server name"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        '.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        '.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Update
    End With
    
    With iMsg
        Set .Configuration = iConf
        .To = "put in the To address here"
        .CC = ""
        .BCC = ""
        .From = "put in the from address here"
        .Subject = "Retransmit Request - " & Range("C5").Value
        .AddAttachment "C:\EDI.xls"
        '.TextBody = strbody
        .Send
    End With
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub