Excel Salary payslip save as pdf and automatically send email using outlook

[witsonjoyet]

[h=2]Dear Sir,[/h]

This is my first Email. Would you please give solution for my query. I am using Excel 2007 and have to send monthly payslips to respective email ids. I have the Name and Email ids in Sheet 2 Range B1:C59 , and employee codes in A1:A59. And in Sheet 1 i have the Payslip format which was automatically displayed when i select the employee code from drop down list in the cell E7 in sheet 1.

Every time i manually save the files as PDF and send to their Emails. I want the out put as whenever i select the employee code from drop down list , it should automatically save as PDF with Name of that employee ( Name of the employee is located in B1:B59 in Sheet 2 ) and attachment should automatically go to that employee's email Id. Would you plz help me to solve this problem

Thanks & Regards
Witson Joyet​

 

[AvcGJS]

Not a full solution but Hopefully the below will give you some clues,




Sub Macro39()
'
' Macro39 Macro
'
Dim Outlookapp As Object
Dim Myitem As Object
Dim cell As Range
Dim subj As String
Dim emailaddr As String
Dim recipient As String
Dim bonus As String
Dim msg As String
Dim mailsubj As String
Dim msgcontent As String
Dim emailaddy As String
Dim emailaddyCC As String
'
Sheets("Payslip").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:file location", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False


'email

'what you want the email to say msg=msg as much as you want chr13 means hit the return key


msg = msg & "Here is your Payslip" & Chr(13) & Chr(13) & Chr(13)






'Sheets("Mailing List").Visible = True


'place the email addresses you want on the sheet user can then replace a name if necessary


Sheets("Sheet2").Select




emailaddy = Range("c5")








Set Outlookapp = CreateObject("Outlook.Application")
subj = "pay "
emailaddress = emailaddy
CCRecipients = emailaddyCC




'gets outlook going, defines subject, who to send to, subject and message


Set Myitem = Outlookapp.createitem(0)
With Myitem
.To = emailaddress
.CC = CCRecipients
.Subject = subj
.body = msg

'need to know where file is saved

.Attachments.Add "file location"
.Display

' ActiveWorkbook.Save
'ActiveWindow.Close
End With

End Sub

 

[witsonjoyet]

Thanks for your reply sir,

I have worked on the code you have provided. its greatly used to me. But i have faced few problems in this macro. Kindly suggest me to solve this problems

when ever i select the code from drop down list from sheet 1 i.e payslip sheet Range E7 )
1) Its not automatically saved on the name of the employee
2) Email id not picked up automatically ( Actual Email Id data is in second sheet i.e Mailing list Range C2:C60 & Names for the correspondence codes are in Range B2: B60)

I have pasted the macro as follows

Sub Macro39()
'
' Macro39 Macro
'
Dim Outlookapp As Object
Dim Myitem As Object
Dim cell As Range
Dim subj As String
Dim emailaddr As String
Dim recipient As String
Dim bonus As String
Dim msg As String
Dim mailsubj As String
Dim msgcontent As String
Dim emailaddy As String
Dim emailaddyCC As String
'


Sheets("Payslip").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Documents and Settings\SYSTEM2\Desktop\payslip.pdf", Quality:=xlQualityStandard _ -------->here i need the name of the employee
, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
'email


'what you want the email to say msg=msg as much as you want chr13 means hit the return key




msg = msg & "Please find the attached salary slip for xxxxx month " & Chr(13) & Chr(13) & Chr(13) is there any possibility to display the previous month name ?


Sheets("Mailing list").Visible = ture
'place the email addresses you want on the sheet user can then replace a name if necessary


'Sheets("mailing list").Select


emailaddy = Range("c5")


Set Outlookapp = CreateObject("Outlook.Application")
subj = "payslip " -----> is it possible to update the previous month name automatically ?
emailaddress = emailaddy
CCRecipients = emailaddyCC








'gets outlook going, defines subject, who to send to, subject and message




Set Myitem = Outlookapp.createitem(0)
With Myitem
.To = emailaddress
.CC = CCRecipients
.Subject = subj
.body = msg


'need to know where file is saved


.Attachments.Add "C:\Documents and Settings\SYSTEM2\Desktop\payslip.pdf" ---> here also the name has to be automatically changed
.Display


' ActiveWorkbook.Save
'ActiveWindow.Close
End With


End Sub




Kindly help me to solve this problem.
one more time i thank you for your valuable suggestions.

Thanks & Regards

Witson Joyet .T