none
Emailing Excel with CDO via VBA RRS feed

  • Question

  • Hello - 

    I have some code I've pieced together (from various sources on this site thank you!) and have run into an issue with part of the code. I am attempting to pull email contact information from within the Excel workbook itself. I have other areas of the code that pulls from the workbook successfully; however I cant seem to figure out how to pull the code the particular to place in the .CC and .From areas of the code. I have isolated to these areas ans the code work great when leaving blank (""). The highlighted/bolded area below is the area I am trying to resolve...any assistance will be greatly appreciated. Thanks in advance!

    I receive the following error:

    Option Explicit

    Sub Email_1()

       

        'This procedure will email the entire workbook

        Dim wb As Workbook

        Dim TempFilePath As String

        Dim TempFileName As String

        Dim FileExtStr As String

        Dim FileFormatNum As Long

        Dim iMsg As Object

        Dim iConf As Object

        Dim Flds As Variant

        Set wb = ActiveWorkbook

        With Application

            .ScreenUpdating = False

            .EnableEvents = False

        End With

       

        'Creates temp file

       

        TempFilePath = Environ$("temp") & "\"

        TempFileName = "New Equipment Request (1) - " & Range("E15") & " - Due Date - " & Format(Range("D9"), "dd-mm-yyyy")

        FileExtStr = ".xlsm": FileFormatNum = 52

        wb.SaveCopyAs TempFilePath & TempFileName & FileExtStr

       

        'Sends workbook via CDO email

       

        Set iMsg = CreateObject("CDO.Message")

        Set iConf = CreateObject("CDO.Configuration")

            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") = "email.nationalcore.org"

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

                .Update

            End With

        With iMsg

            Set .Configuration = iConf

            .To = "rstreet@nationalcore.org"

            .CC = ThisWorkbook.Sheets("Equipment Request Form").Range("E16").Value

            .BCC = ""

            .From = ThisWorkbook.Sheets("Equipment Request Form").Range("E16").Value

            .Subject = "Test - Step 1 - To Accent - New Equipment Request - " & Range("E15") & " - Due Date: " & Range("D9")

            .TextBody = "Attention ACCENT: Attached is a New Equipment Request Form for " & Range("E15") & " for review. Please review and provide any applicable proposals. Once reviewed please sign to submit for processing." & vbNewLine & vbNewLine & "Thank you," & vbNewLine & Range("E17")

            .AddAttachment TempFilePath & TempFileName & FileExtStr

            .Send

        End With

        'Kills the temp file

       

        Kill TempFilePath & TempFileName & FileExtStr

        With Application

            .ScreenUpdating = True

            .EnableEvents = True

        End With

       

        Application.Quit   

       

    End Sub

    Thursday, December 17, 2015 5:06 PM

Answers

  • Hi all - So with a little more digging and playing I was able to come up with something that suits my needs. It appears the email address formatting was off when pulling data from the workbook. I added a little something to the code and now it work like a charm.

    Here is what I can up with...

    • Marked as answer by rstreets2 Thursday, December 17, 2015 7:23 PM
    Thursday, December 17, 2015 7:23 PM