PDF Reports from excel

[nikhil83]

Hi,

I have a report which has to be sent in PDF format to some people picked from an excel table.The data in this report is also picked from the same table.All actions should be done on a button click . On button click,the logic should match person's name and sheet name(Were report pertaining to that person will be placed),convert this report to PDF Format and send it to the mail id mentioned against the person's name in the table.

Please let me know how this can be done.

 

[Deepak]

Can we have the said xl pls.

 

[nikhil83]

Can we have the said xl pls.

In the dummy Excel attached, i have created only 3 sheets with nominations in table . If i press on send report button in Master sheet,Logic should match nominations with sheet name,convert sheet into a PDF with same name and fetch the mail id of the person from master table and send PDF file to this person automatically through outlook.

 

[Deepak]

Check this..

Sub t()
Dim r As Range, strfilename As String, body_txt As String, mypdf As String

For Each r In [master_T[Nominations]]

strfilename = CreateObject("Wscript.Shell").SpecialFolders("Desktop") _
    & Application.PathSeparator

mypdf = strfilename & r.Value & ".pdf"

Sheets(r.Value).ExportAsFixedFormat Type:=xlTypePDF, Filename:=mypdf, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

body_txt = "<font face=Calibri size=3 color=green>Hi,<br><br>" & _
          "Pls. find herewith enclosed.<br><br><br><br></font>BR<br>"

    With CreateObject("Outlook.Application").CreateItem(0)
        .To = r.Offset(, 1).Value
'        .CC = ""
      ' .BCC = ""
        .Subject = r.Value
'        .BodyFormat = .olFormatHTML
        .HTMLBody = body_txt
        .Attachments.Add mypdf
        .Display
        '.Send
    End With
Next

End Sub