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

[jacobsky]

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?

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

 

[jacobsky]

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!