locked
Add Attachment Assistance RRS feed

  • Question

  • Greetings -

    I am in need of some assistance with adding an attachment. I keep receiving a Run-time error "13": Type mismatch error when using the .Attachments.Add (highlighted below) thingy majig ;-)

    See code below. Anyone have any ideas as to why this may not be working and/or suggestions on how to get it to work?

    Any assistance is always appreciated.  :-)

    Here's what I got thus far...

    -------------------------------------------------------------------------------------------

    Option Explicit

    Public Sub SubmitForm()

     

        Dim R As Long

        Dim wb As Workbook

        Dim ObjExcel As Excel.Application

        Dim FullPath As String

        Dim FilePath As String

        Dim fileName 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 file path, saves and names workbook      '

    ''''''''''''''''''''''''''''''''''''''''''''''''''''’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’

       

    FilePath = C:\Users\rstreet\Desktop\Testing Folder\

    fileName = "Incident Report #" & Sheets("Accident-Incident Report").Range("N4") & " - " & Sheets("Accident-Incident Report").Range("C5") & " - " & Format(Sheets("Accident-Incident Report").Range("C9"), "mm-dd-yyyy") & " - " & Sheets("Accident-Incident Report").Range("I9")

        FileExtStr = ".xlsm": FileFormatNum = 52

       

        FullPath = FilePath & fileName & FileExtStr

       

        wb.SaveCopyAs (FullPath)  (works!)

       

    ''''''''''''''''''''''''''''''''''''''’’’’’’’’’’’’’’’’’’’’’’’’’’’’

    '  Sends Attachment 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") = "emailserver_address"

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

                .Update

            End With

        With iMsg

            Set .Configuration = iConf

            '.To = "Risk Management (insert incident reports email)"

            .To = "Randy Street (rstreet@nationalcore.org)"

           .From = "Me Myself & I (rstreet@nationalcore.org)"

           ‘ .From = Sheets("Accident-Incident Report").Range("C7").Value & " (" & Sheets("Accident-Incident Report").Range("I82").Value & "@nationalcore.org)"

            .Subject = “Test”

            .HTMLBody = “Test”

               

                .Attachments.Add FullPath    (have tried various methods below as well)

                    '.Attachments.Add FilePath & fileName & FileExtStr

                    '.Attachments.Add (FullPath)

                    '.Attachments.Add (FilePath & fileName & FileExtStr)

               

                .Send

        End With   

     

        R = MsgBox("Finally Worked.", vbInformation)

     

                    'R = MsgBox("Your request has been submitted.", vbInformation)  

                     'Call Reset_Submit 'located in Reset2_Submit module   

                     'wb.Close savechanges:=True

       

    End Sub

    Friday, March 23, 2018 5:54 PM

Answers

  • Ok...here I go again...after a nice lunch I figured it out...

    Here is the answer should anyone be interested:
               
                iMsg.AddAttachment FullPath

    Thank you to anyone who has spent any time on this. Your efforts are much appreciated.

    Thank you.

    • Marked as answer by rstreets2 Friday, March 23, 2018 9:03 PM
    Friday, March 23, 2018 9:03 PM

All replies

  • Hello -

    Just though I would add a note...I can add the following to the end of the .HTMLBody and it works perfectly. Not understanding why I am unable to add attachment though?

    '& _

    "<Font Size = 3 Color = Black Face = Calibri>Click on this link to open the file:        " & _

    "<A HREF=""file://" & FullPath & _

    """>Link to the file</A>"

    Friday, March 23, 2018 6:15 PM
  • Ok...here I go again...after a nice lunch I figured it out...

    Here is the answer should anyone be interested:
               
                iMsg.AddAttachment FullPath

    Thank you to anyone who has spent any time on this. Your efforts are much appreciated.

    Thank you.

    • Marked as answer by rstreets2 Friday, March 23, 2018 9:03 PM
    Friday, March 23, 2018 9:03 PM