Loop Through Combobox, Print to PDF, and Email Each Item Separately

jacobsky

New Member
Joined
Mar 19, 2018
Messages
8
What I am hoping to do is it loop through my ComboBox about 200+ Items. For each item in the ComboBox perform the combobox1_Change function to recalculate sheet with the code I have in there and then run this code to print to PDF, and attach as an email for each item(each Item has its own email address).

When trying to research this, I've seen other posts asking if this is on a "Userform". I am not sure what difference that would make as I am not too familiar with userforms.

Any suggestions?


Code:
Private Sub CommandButton2_Click()
  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
  Dim Emailaddress As String
  


  Emailaddress = Sheets("DataPivotTables").Range("A3").Value
 
  ' Not sure for what the Title is
  Title = "Provider Dashboard"
 
  ' Define PDF filename
  PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & Sheets("Provider Dashboard 1").Range("A3").Text & ".pdf"
 
  ' Export activesheet as PDF
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    ' Prepare e-mail
    .Subject = Title
    .To = Emailaddress ' <-- Put email of the recipient here
    .Body = "Hi," & vbLf & vbLf _
          & "Attached is your Monthly Provider Dashboard.  Please contact your director if you have any questions." & vbLf & vbLf _
            & "Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Display
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent", vbExclamation
    Else
      MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0
   
  End With
 
  ' Delete PDF file
  Kill PdfFile
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
 
 
End Sub
 
Last edited:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Replying to bump.

To clarify my original post, this is not on a userform.

Anyone have any suggestions on how to modify the code above to loop through all the combobox items 1 at a time, perform my Combobox_Change() code, and then perform the code above. I have tried to add some code around the above but it doesnt actually recalculate the spreadsheet with the new combobox item. I should say I have almost no coding experience and just try to play around with code until I get it to work but havent had luck on this one yet.

Dim intComboItem As Integer

For intComboItem = 0 To Me.ComboBox1.ListCount - 1



Next
Thanks!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top