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("") = 2

                .Item("") = ""

                .Item("") = 25


            End With

        With iMsg

            Set .Configuration = iConf

            .To = ""

            .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


        End With

        'Kills the temp file


        Kill TempFilePath & TempFileName & FileExtStr

        With Application

            .ScreenUpdating = True

            .EnableEvents = True

        End With




    End Sub

    Thursday, December 17, 2015 5:06 PM


  • 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