sending multiple attachments macro

[daveyc18]

so i send a bunch of PDF files as "file name mm-dd" every day where the date is BASED on the previous business day ...also the subject = "title as of mm dd, yyyy"

in the title, I prefer (not necessary) the date format to be May 1, 2015


path of files: G:\a\b\c\yyyy-mm\mm-dd ...here the date format is "2015-05\05-01"

is there a way to macro this via Excel or even outlook itself?

 

[Michael M]

See here to send E-mails from Excel via Outlook


Mail from Excel with Outlook (VBA)

 

[daveyc18]

See here to send E-mails from Excel via Outlook


Mail from Excel with Outlook (VBA)

only issue with this macro is that whever the macro button is placed (e.g.," sheet2"), the macro sends the file based on the last sheet u were on....my colleague doesn't want sheet1 to contain any macro buttons...anyway to click the button on sheert2 and then have the file sent where when it's open via email, sheet1 appears?

 

[daveyc18]

only issue with this macro is that whever the macro button is placed (e.g.," sheet2"), the macro sends the file based on the last sheet u were on....my colleague doesn't want sheet1 to contain any macro buttons...anyway to click the button on sheert2 and then have the file sent where when it's open via email, sheet1 appears?

Sub SendFile()
Dim wb1 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object


    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With


    Set wb1 = ActiveWorkbook


TempFilePath = Environ$("temp") & "\"
    TempFileName = wb1.Name
  


    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    Sheets("Sheet2").Select
    
    ActiveWorkbook.EnvelopeVisible = True
    Range("L21:M27").Select


    
    Call setdate
    
    
    On Error Resume Next
    With ActiveSheet.MailEnvelope
         .Item.To = "david_choi@cibcmellon.com"
        .Item.cc = ""
        .Item.Subject = "Treasury Stats for " & m5 & " " & prevd & " " & "," & prevy
        .Item.Attachments.Add TempFilePath & TempFileName
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Item.send   'or use .Display
    End With
    On Error GoTo 0


    'Delete the file
    Kill TempFilePath & TempFileName & FileExtStr


    Set OutMail = Nothing
    Set OutApp = Nothing


    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
    Sheets("Sheet1").Select
    ActiveWorkbook.Save
  


   
End Sub

 

[daveyc18]

so i send a bunch of PDF files as "file name mm-dd" every day where the date is BASED on the previous business day ...also the subject = "title as of mm dd, yyyy"

in the title, I prefer (not necessary) the date format to be May 1, 2015


path of files: G:\a\b\c\yyyy-mm\mm-dd ...here the date format is "2015-05\05-01"

is there a way to macro this via Excel or even outlook itself?

sorry just bumping this

 

[Michael M]

Ok, so run the code using ALT + F8, or or add the code to a button on the QAT and run it when the rquired sheet is active !!

 

[daveyc18]

How about sending pdfs ? The Web site seems to only be regarding excel files and worksheets...I know this isn't an outlook forum but maybe someone familiar with vba knows how to solve my problem via outlook