+ Reply to Thread
Results 1 to 13 of 13

Select checkboxes on a userform to save to PDF and e-mail

Hybrid View

  1. 09-18-2014, 04:05 PM #1
    Kolein
    Kolein is offline
    Registered User
    Join Date
    09-18-2014
    Location
    South Africa
    MS-Off Ver
    Office 365
    Posts
    7

    Select checkboxes on a userform to save to PDF and e-mail

    Hi. I have created a userform with checkboxes to select which sheets of my workbook must be saved to PDF and emailed. Currently the code is working, but the only problem is that not only the selected sheet is saved but all are saved. I need help on how to change my code for only the selected sheets to be saved and emailed.

    Form.jpg

    Private Sub CommandButton1_Click()

If CheckBox1 = True Then
Sheets("Confirm").Visible = True
Else
End If
      If CheckBox2 = True Then
      Sheets("Work Order").Visible = True
      Else
      End If
            If CheckBox3 = True Then
            Sheets("Material").Visible = True
            Else
            End If
                  If CheckBox4 = True Then
                  Sheets("E-Plan").Visible = True
                  Else
                  End If
                  
Sheets(Array("Confirm", "Work Order", "Material", "E-Plan")).Select
    Sheets("Confirm").Activate
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        Environ("Userprofile") & "\Documents\Emailed Jobs\" & Sheets("Confirm").Range("BA1") & ".pdf", quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        
Dim OutApp As Object
Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
     
On Error Resume Next

With OutMail
    .To = Sheets("Confirm").Range("BA2") & Sheets("Confirm").Range("BA3") & Sheets("Confirm").Range("BA4") & Sheets("Confirm").Range("BA5")
    .CC = Environ("Username") & "@xxxxxx.co.za"
    .BCC = "xxxxxx@xxxxxx.co.za"
    .Subject = "Job ready for Execution" & " " & Sheets("Confirm").Range("BA1")
    .Body = "Hi." & vbNewLine & vbNewLine & "Please execute the job as per starting and completion dates." & vbNewLine & vbNewLine & "Regards" & vbNewLine & vbNewLine & "CR Section"
    
    .Attachments.Add (Environ("Userprofile") & "\Documents\Emailed Jobs\" & Sheets("Confirm").Range("BA1") & ".pdf")
    .Send
    
MsgBox ("Congratulations!" & vbNewLine & vbNewLine & "The job has been saved to PDF and E-mailed")

End With
On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing
    
    Range("AU15").Select
    Sheets("Confirm").Select
    
 End Sub
    Reply With QuoteRegister To Reply
  2. 09-19-2014, 11:17 AM #2
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Select checkboxes on a userform to save to PDF and e-mail

    Hi Kolein

    Welcome to the Forum!!

    This Code is untested...if you wish tested Code please attach your File WITH Code. 
    Option Explicit

Private Sub CommandButton1_Click()
  Dim mySheet As String
  If Me.CheckBox1 = True Then
    Sheets("Confirm").Visible = True
    mySheet = "Confirm"
  Else
  End If
  If CheckBox2 = True Then
    Sheets("Work Order").Visible = True
    mySheet = "Work Order"
  Else
  End If
  If CheckBox3 = True Then
    Sheets("Material").Visible = True
    mySheet = "Material"
  Else
  End If
  If CheckBox4 = True Then
    Sheets("E-Plan").Visible = True
    mySheet = "E-Plan"
  Else
  End If

  '  Sheets(Array("Confirm", "Work Order", "Material", "E-Plan")).Select
  Sheets(mySheet).Activate
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                  Environ("Userprofile") & "\Documents\Emailed Jobs\" & Sheets(mySheet).Range("BA1") & ".pdf", quality:= _
                                  xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                                  OpenAfterPublish:=False

  Dim OutApp As Object
  Dim OutMail As Object

  Set OutApp = CreateObject("Outlook.Application")
  Set OutMail = OutApp.CreateItem(0)

  On Error Resume Next

  With OutMail
    .To = Sheets(mySheet).Range("BA2") & Sheets(mySheet).Range("BA3") & Sheets(mySheet).Range("BA4") & Sheets(mySheet).Range("BA5")
    .CC = Environ("Username") & "@xxxxxx.co.za"
    .BCC = "xxxxxx@xxxxxx.co.za"
    .Subject = "Job ready for Execution" & " " & Sheets(mySheet).Range("BA1")
    .Body = "Hi." & vbNewLine & vbNewLine & "Please execute the job as per starting and completion dates." & vbNewLine & vbNewLine & "Regards" & vbNewLine & vbNewLine & "CR Section"

    .Attachments.Add (Environ("Userprofile") & "\Documents\Emailed Jobs\" & Sheets(mySheet).Range("BA1") & ".pdf")
    .send
    MsgBox ("Congratulations!" & vbNewLine & vbNewLine & "The job has been saved to PDF and E-mailed")

  End With
  On Error GoTo 0


  Set OutMail = Nothing
  Set OutApp = Nothing

  Range("AU15").Select
  Sheets(mySheet).Select

End Sub
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
    Reply With QuoteRegister To Reply
  3. 09-20-2014, 07:11 AM #3
    Kolein
    Kolein is offline
    Registered User
    Join Date
    09-18-2014
    Location
    South Africa
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Select checkboxes on a userform to save to PDF and e-mail

    Hi Jaslake

    I have tried similar code then yours and could not get it working. No matter which checkbox is ticked it only saves the Confirm sheet to pdf. the problem is with the sheets array to select all the sheets. Attached is a file to test. Thanks
    Attached Files Attached Files
    Last edited by Kolein; 09-20-2014 at 10:27 AM.
    Reply With QuoteRegister To Reply
  4. 09-20-2014, 11:26 AM #4
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Select checkboxes on a userform to save to PDF and e-mail

    Hi Kolein

    In looking at the revisions in the Code I'm not certain what it is you're trying to do. Please explain in words.
    Reply With QuoteRegister To Reply
  5. 09-20-2014, 12:11 PM #5
    Kolein
    Kolein is offline
    Registered User
    Join Date
    09-18-2014
    Location
    South Africa
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Select checkboxes on a userform to save to PDF and e-mail

    On the userform is 6 checkboxes, If i only need to select 2 of the or 3 or all six or only one. I want only to save those where the checkboxes has been checked to pdf and email them. Not all the pages is always applicable, say maybe the first 3 is needed, next time all six is needed to be emailed. I need to be able to choose which pages/sheets I want to save and the email.
    Reply With QuoteRegister To Reply
  6. 09-20-2014, 12:17 PM #6
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Select checkboxes on a userform to save to PDF and e-mail

    Hi Kolein

    Will the PDF always be named this? 
     "\Documents\Emailed Jobs\" & Sheets("Confirm").Range("BA1") & ".pdf"
    You cannot have two PDF's of the same name...
    Reply With QuoteRegister To Reply
  7. 09-20-2014, 12:50 PM #7
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Select checkboxes on a userform to save to PDF and e-mail

    Hi Kolein

    Try this for your Command Button Code...notice the Control Tip Text for each Check Box... 
    Private Sub CommandButton1_Click()

  Dim SheetsFound()
  ReDim SheetsFound(0)
  For i = 1 To 6
    If Me.Controls("CheckBox" & i).Value = True Then
      SheetsFound(UBound(SheetsFound)) = Me.Controls("CheckBox" & i).ControlTipText
      ReDim Preserve SheetsFound(UBound(SheetsFound) + 1)
    End If
  Next i
  ReDim Preserve SheetsFound(UBound(SheetsFound) - 1)
  Sheets(SheetsFound).Select

  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                  Environ("Userprofile") & "\Documents\Emailed Jobs\" & Sheets("Confirm").Range("BA1") & ".pdf", quality:= _
                                  xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                                  OpenAfterPublish:=False
  Dim OutApp As Object
  Dim OutMail As Object

  Set OutApp = CreateObject("Outlook.Application")
  Set OutMail = OutApp.CreateItem(0)

  On Error Resume Next

  With OutMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "Job ready for Execution" & " " & Sheets("Confirm").Range("BA1")
    .Body = "Hi." & vbNewLine & vbNewLine & "Please execute the job as per starting and completion dates." & vbNewLine & vbNewLine & Sheets("Confirm").Range("BA16") & vbNewLine & vbNewLine & "Regards" & vbNewLine & vbNewLine & Sheets("Confirm").Range("J24")

    .Attachments.Add (Environ("Userprofile") & "\Documents\Emailed Jobs\" & Sheets("Confirm").Range("BA1") & ".pdf")
    .Send

    MsgBox ("Congratulations!" & vbNewLine & vbNewLine & "The job has been saved to PDF and E-mailed")

  End With
  On Error GoTo 0


  Set OutMail = Nothing
  Set OutApp = Nothing

  Range("AU15").Select
  Sheets("Confirm").Select
End Sub
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 09-21-2014, 02:47 AM #8
    Kolein
    Kolein is offline
    Registered User
    Join Date
    09-18-2014
    Location
    South Africa
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Select checkboxes on a userform to save to PDF and e-mail

    Hi Jaslake.

    To answer your question about the name of the PDF - yes it will always save as that, because for each job a new file is created and it has a unique reference number and with formulas this reference is then used to save it to PDF.

    I need to thank you as I have tested the code and it works. I can add more checkboxes as the need arise. The control tip text is very important otherwise an error message of subscript out of range pops up.

    Thank you very much for your help, I have learned something new.

    Regards

    Kolein
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Select series(lines) in a line graph by adding userform with checkboxes on a chart
    By aab_489 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-24-2014, 12:25 PM
  2. VBA Userform: Warn user if all checkboxes on a userform are unticked.
    By MrMiyagi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-07-2014, 12:08 AM
  3. Extract XLS* attachments from select mails, save mail info into fields
    By Alteregoist in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-14-2013, 05:35 AM
  4. Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro
    By krackaberr in forum Excel Programming / VBA / Macros
    Replies: 34
    Last Post: 03-05-2013, 11:12 AM
  5. Userform vlookup with the ability to save or select values
    By jpruffle in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-06-2009, 12:16 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1