Results 1 to 12 of 12

Email range to attach

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2024
    Posts
    2,873

    Email range to attach

    I have written code to attach a range which I have named Journals on sheet "Data"


    The range name for "Journals is A20:D24


    When running the macro, the workbook close and the sheet for the range "Journals" is not attached

    It would be appreciated if someone could check my code and kindly amend it


     Sub Email_Journal()
    
    Dim File As String, strBody As String
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    File = Environ$("temp") & ".xlsx"
    
    strBody = "Hi " & Sheets("Data").Range("AT1") & vbNewLine & vbNewLine & _
            "Attached, please find Journal Entries to be printed" & vbNewLine & vbNewLine & _
                "Regards" & vbNewLine & vbNewLine & _
                "Howard"
    
    Set Rng = Nothing
       
        
    Sheets("Data").Range("Journals").Copy
      
        
    With ActiveWorkbook
       .SaveAs Filename:=File, FileFormat:=51
       .Close savechanges:=False
    End With
    With CreateObject("Outlook.Application").CreateItem(0)
        .Display
       
    
        
               .To = Join(Application.Transpose(Sheets("Data").Range("AU1:AU2").Value), ";")
    
                .Subject = Sheets("Data").Range("a42")
               .body = strBody
               .Attachments.Add File
              
    End With
    Kill File
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to create an email, attach current workbook and also paste a range from sheet
    By StormFusion in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-12-2019, 08:32 AM
  2. [VBA] Save Range as picture, save to file, attach to email
    By Armitage2k in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-23-2018, 07:34 AM
  3. Macro to attach range to email in new sheet but not send?
    By Serafin54 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-22-2014, 01:07 PM
  4. attach different worksheet and email them tdifferent email address through macro/vba/addin
    By arunverma004 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-03-2014, 08:20 AM
  5. [SOLVED] Email Macro to attach a non active worksheet to outlook email
    By mickgibbons1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-21-2013, 08:38 PM
  6. SaveAs PDF and attach to email macro won't attach?!
    By Rerock in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2012, 05:28 PM
  7. copy range of cells to a new tab and attach in an email
    By dzuspann in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-23-2012, 04:14 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1