vendredi 16 juillet 2021

Trying to make a userform with Checkboxes to choose which sheets need to be exported as a PDF and put in a mail

Hi I'am an beginner but I'm looking for a way where I can connect a userform with checkboxes so I can choose which sheets I want to export as a pdf and send with the following sub: I'll also include the file with the userform.

      Sub Saveaspdfandsend()
'dim everything
    Dim xSht As Worksheet
    Dim xFileDlg As FileDialog
    Dim xFolder As String
    Dim xYesorNo As Integer
    Dim xOutlookObj As Object
    Dim xEmailObj As Object
    Dim xUsedRng As Range
     
    Set xSht = ActiveSheet
    Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
     
    If xFileDlg.Show = True Then
       xFolder = xFileDlg.SelectedItems(1)
    Else
       MsgBox "You must specify a folder to save the PDF into." & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Must Specify Destination Folder"
       Exit Sub
    End If
    xFolder = xFolder + "\" + xSht.Name + ".pdf"
     
    'Check if file already exist
    If Len(Dir(xFolder)) > 0 Then
        xYesorNo = MsgBox(xFolder & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it?", _
                          vbYesNo + vbQuestion, "File Exists")
        On Error Resume Next
        If xYesorNo = vbYes Then
            Kill xFolder
        Else
            MsgBox "if you don't overwrite the existing PDF, I can't continue." _
                        & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Exiting Macro"
            Exit Sub
        End If
        If Err.Number <> 0 Then
            MsgBox "Unable to delete existing file.  Please make sure the file is not open or write protected." _
                        & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"
            Exit Sub
        End If
    End If
     
    Set xUsedRng = xSht.UsedRange
    If Application.WorksheetFunction.CountA(xUsedRng.Cells) <> 0 Then
        'Save as PDF file
        xSht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=xFolder, Quality:=xlQualityStandard
         
        'Create Outlook email
        Set xOutlookObj = CreateObject("Outlook.Application")
        Set xEmailObj = xOutlookObj.CreateItem(0)
        With xEmailObj
            .Display
            .To = ""
            .CC = ""
            .Subject = xSht.Name + ".pdf"
            .Attachments.Add xFolder
            If DisplayEmail = False Then
                '.Send
            End If
        End With
    Else
      MsgBox "The active worksheet cannot be blank"
      Exit Sub
    End If
    End Sub   I

I am looking for a way in which i can choose which sheets i want to export and not. I am planning to do that with a userform with checkboxes.

if you have a better solution let me know!

kind regards!




Aucun commentaire:

Enregistrer un commentaire