Consulting

Results 1 to 8 of 8

Thread: VBA Code to attach (multiple) file(s)

  1. #1

    VBA Code to attach (multiple) file(s)

    Hi
    I try to attach (multiple) file(s) from the sheet (Files).
    To select (multiple) file(s), I insert * in excel cell (C) to the desired path (Cell B). After the selection, I can run the macro from (Configurator) sheet. Right now, I already have a VBA macro that creates an email without attachment.

    I need a separate VBA code, which should attach (multiple) selected file(s) to the email.

    Can someone help me to solve my problem?

    thx

    tom
    Attached Images Attached Images

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Tom, you should show the code that macro that creates an email without attachment.

    Click the # Icon and paste the code between the Code Formatting Tags
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Hi Same

    I’m using macros from Ron de Bruin: rondebruin.nl/win/s1/outlook/bmail2.htm
    thx
    tom

  4. #4
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    That code is for selected cells, not files. As SamT said, please post your code using the Code tags.
    Semper in excretia sumus; solum profundum variat.

  5. #5
    You probably want something like the following - note that this uses code from Ron's web site to start Outlook - http://www.rondebruin.nl/win/s1/outlook/openclose.htm and assumes that the worksheet illustrated is the active sheet.

    Sub SendWithAttachments()
    Dim olApp As Object
    Dim olMail As Object
    Dim xlSheet As Worksheet
    Dim fso As Object
    Dim LastRow As Long, lngRow As Long
    
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set xlSheet = ActiveSheet
        With xlSheet
            LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        End With
        Set olApp = OutlookApp()
        Set olMail = olApp.createitem(0)
        With olMail
            .Subject = "Please find fies attached"
            .display
            For lngRow = 9 To LastRow
                If InStr(1, xlSheet.Cells(lngRow, 3), "*") > 0 Then
                    If fso.FileExists(xlSheet.Cells(lngRow, 2)) = True Then
                        .attachments.Add xlSheet.Cells(lngRow, 2)
                    End If
                End If
            Next lngRow
        End With
        On Error GoTo 0
    lbl_Exit:
        Set fso = Nothing
        Set olApp = Nothing
        Set olMail = Nothing
        Exit Sub
    End Sub
    Last edited by gmayor; 01-27-2018 at 01:11 AM.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  6. #6
    Hi Graham
    I have got an Error …. "Sub or Function not defined"
    Set olApp = OutlookApp


    And it doesn't matter whether or not Outlook is running .

    t

  7. #7
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    OutlookApp()
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  8. #8
    I guess you missed
    note that this uses code from Ron's web site to start Outlook - http://www.rondebruin.nl/win/s1/outlook/openclose.htm
    which code provides that function?
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •