PDF VBA that will recreate for dynamic cell

[JAnders]

Hello,
I have a large workbook (27000 KB) that I'm trying to prevent people from having to open. On "Sheet1" I have a page that has a dropdown on A1, the dropdown is a list of 40 names from "Sheet2" A$1:A$40. The goal is to create a PDF from "Sheet1" cells A1:Z50 and sent to the email address located on cell A3. I want it to repeat this process until all 40 names have completed. Please let me know if any further details are needed.


Appreciate and help on this.

Thanks
JAnders

 

[AFPathfinder]

Is this a pre-2007 Excel file? If so, I'd suggest saving it as a newer format (xlsx) to reduce the size.

If all 40 names are getting the same file/information, why not just have it generate one email to all 40 names at once?

Does it have to be a PDF or can it be a new workbook (A1:Z50 would be a relatively small amount of data)?

 

[JAnders]

Is this a pre-2007 Excel file? If so, I'd suggest saving it as a newer format (xlsx) to reduce the size.

If all 40 names are getting the same file/information, why not just have it generate one email to all 40 names at once?

Does it have to be a PDF or can it be a new workbook (A1:Z50 would be a relatively small amount of data)?

I have 2016 Excel and have it saved in xlsb. format (which I thought saved space).
The reason I don't have it send 1 email to 40 different people is the name in A1 (drop down) is what most of the data from A1:Z50 is dependent on. As I change the name it changes all data in A1:Z50, the content is specific to the individual listed on A1 which is whom I want the email going to.

 

[AFPathfinder]

This is untested, but should be relatively close to what you're looking for:

Sub ValidationList_PDFPrint()
    Dim inputRange As Range
    Dim cell As Range
    Dim OutApp As Object
    Dim OutMail As Object
    
    Set inputRange = Evaluate(Range("A1").Validation.Formula1)
    Set OutApp = CreateObject("Outlook.Application")
    
    For Each cell In inputRange
        Range("A1").Value = cell.Value
        
        ActiveSheet.Range("A1:Z50").ExportAsFixedFormat Type:=xlTypePDF, filename:= _
            "C:\Users\" & Environ("USERNAME") & "\Desktop\" & cell.Value & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=False
        
        Set OutMail = OutApp.CreateItem(olMailItem)
    
        With OutMail
            .To = ""
            .CC = ""
            .Subject = ThisWorkbook.Name & " Daily Update - " & cell.Value
            .Attachment.Add "C:\Users\" & Environ("USERNAME") & "\Desktop\" & cell.Value & ".pdf"
            .body = ""
            .display
        End With
        x = x + 1
        
        Set OutMail = Nothing
    Next cell
    Set OutApp = Nothing
End Sub

 

[JAnders]

This is untested, but should be relatively close to what you're looking for:

Sub ValidationList_PDFPrint()
    Dim inputRange As Range
    Dim cell As Range
    Dim OutApp As Object
    Dim OutMail As Object
    
    Set inputRange = Evaluate(Range("A1").Validation.Formula1)
    Set OutApp = CreateObject("Outlook.Application")
    
    For Each cell In inputRange
        Range("A1").Value = cell.Value
        
        ActiveSheet.Range("A1:Z50").ExportAsFixedFormat Type:=xlTypePDF, filename:= _
            "C:\Users\" & Environ("USERNAME") & "\Desktop\" & cell.Value & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=False
        
        Set OutMail = OutApp.CreateItem(olMailItem)
    
        With OutMail
            .To = ""
            .CC = ""
            .Subject = ThisWorkbook.Name & " Daily Update - " & cell.Value
            .Attachment.Add "C:\Users\" & Environ("USERNAME") & "\Desktop\" & cell.Value & ".pdf"
            .body = ""
            .display
        End With
        x = x + 1
        
        Set OutMail = Nothing
    Next cell
    Set OutApp = Nothing
End Sub

Thank you for your prompt response!!
it seems to be saving the PDF file for the first name and then errors out when it tries to attach the PDF to an email. Error message 438 " object doesn't support this property or method. I did change the file path to where it's saved and attached from, I made them identical as did you, it is saving fine just not attaching?

Because of this error I haven't been able to validate if it will continue until all names have passed through?
As I've ran it multiple times, it does appear to overwrite previous file

Thanks again

 

[AFPathfinder]

That type of error is typical when the library/reference is missing. Make sure your Office Outlook reference is checked.

 

[JAnders]

That type of error is typical when the library/reference is missing. Make sure your Office Outlook reference is checked.

works perfect. I just needed to change to applications instead of application

thanks again

 

[AFPathfinder]

You're welcome!