+ Reply to Thread
Results 1 to 2 of 2

Add additional attachments to email if Check box checked

Hybrid View

  1. #1
    Registered User
    Join Date
    04-15-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 365
    Posts
    49

    Add additional attachments to email if Check box checked

    Hi,
    Tried searching but couldn’t find anything that helped.
    I have inherited a macro that sends the active sheet as an attachment to a recipient (based on a pull down) via email.
    I want to include additional attachments if a checkbox is checked (Linked to cell CA29).
    The code below works, as in when you select the send button it sends the sheet to email as an attachment but the issue I’m having is,
    The selection window pops up for you to select a file to attach whether or not you have the check box checked or not.

    This is the section I added for additional attacents.
    If Range("CA29") = True Then
        .Attachments.Add (wbFullName)
    Else
        End If
    Can someone help please.

    Private Sub Manager_Email_Click()
    
        Dim FileExtStr As String
        Dim FileFormatNum As Long
        Dim Sourcewb As Workbook
        Dim Destwb As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim OutApp As Object
        Dim OutMail As Object
        Dim cell As Range
        Dim strbody As String
        Dim wbFullName As Variant
    
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        Set Sourcewb = ActiveWorkbook
        'Copy the ActiveSheet to a new workbook
        ActiveSheet.Copy
        Set Destwb = ActiveWorkbook
        wbFullName = Application.GetOpenFilename("All Files (*.*), *.*")
        'Determine the Excel version and file extension/format
        With Destwb
            If Val(Application.Version) < 12 Then
                'You use Excel 97-2003
                FileExtStr = ".xls": FileFormatNum = -4143
            Else
                'You use Excel 2007-2013
                Select Case Sourcewb.FileFormat
                Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If .HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                    Else
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                Case 56: FileExtStr = ".xls": FileFormatNum = 56
                Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
                End Select
            End If
        End With
        'Save the new workbook/Mail it/Delete it
        TempFilePath = Environ$("temp") & "\"
        TempFileName = Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        With Destwb
        Call Delete_My_Named_Ranges
            .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
            On Error Resume Next
            With OutMail
                .To = Manager_Name.Value
                .CC = ""
                .BCC = ""
                .Subject = "Approval Required - Training Request Form"
              .Body = "Your approval is needed on this Training Request Form," & vbCrLf & _
        "Please select approved or rejected on the form" & vbCrLf & _
        "and submit the form to the Training Co-Ordinator" & vbCrLf & _
        "If Rejected, please add comments and reply to Initiator of form"
         .Attachments.Add Destwb.FullName
         
         If Range("CA29") = True Then
        .Attachments.Add (wbFullName)
    Else
        End If
    
    
    
                '.Send   'or use .Display
                .Display
    
            End With
            On Error GoTo 0
            
        .Close savechanges:=False
        End With
    MsgBox "This Form has been submited to the Your Selected Manager."
        'Delete the file you have send
        Kill TempFilePath & TempFileName & FileExtStr
        ActiveWorkbook.Close False
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub

  2. #2
    Registered User
    Join Date
    04-15-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 365
    Posts
    49

    Re: Add additional attachments to email if Check box checked

    I have solved this myself with the code below thanks for all those who tried.
    Putting this up in case anyone else needs it.

     If Sheets("Template").Range("CA29").Value <> False Then
         wbAttachment = Application.GetOpenFilename("All Files (*.*), *.*")
         .Attachments.Add (wbAttachment)
          .send
    Else
             .send
               
            End If

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to check if excel file is Checked Out in SharePoing Check Out, run code, Check In
    By jrtraylor in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-08-2016, 04:36 PM
  2. Check Box that shows options in Combo Box if Checked, and Hides if Not Checked
    By dumbjodie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-13-2013, 10:06 AM
  3. Automatically check one or more check boxes when a parent check box is manually checked
    By Steverizer in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-28-2013, 01:56 PM
  4. Replies: 0
    Last Post: 02-21-2013, 04:46 AM
  5. Replies: 2
    Last Post: 12-19-2012, 11:23 PM
  6. Add additional attachments to email VBA application
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-24-2010, 12:22 PM
  7. Print when check box checked
    By vincenealon in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-26-2005, 08:41 AM

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