Sent the pdf payslip automatically through outlook mail

[witsonjoyet]

Dear Sir,

I am using Excel 2007 and Outlook. I have to send payslips to employee everymonth. for this i am using macro enabled work sheet which automatically save as pdf and its attached to empty mail.

In my sheet1 range E7 if i select the code from drop down list then automatically the payslip will updated for that respective employee and name will display in sheet1(Payslip) C5.

In sheet 2(mailing list) i have names and mail ids in Range B2:B59 and C2:C59 respectively.

here i want the macro as when ever i select employee code automatically that employee mail id from Sheet2 i.e mailing list should displayed in To: address in my outlook mail.

My macro is as follows:

Sub payslip()
'
' Payslip 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\" & MonthName((Month(Date)) - 1) & " 2013 Payslip", 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 & "Please find the attached salary slip for " & MonthName((Month(Date)) - 1) & " 2013 " & 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("mailing list").Select


emailaddy = Range("c5 ---> here i need the macro as automatically pick up the mail id


Set Outlookapp = CreateObject("Outlook.Application")
subj = "Pay slip for " & MonthName((Month(Date)) - 1) & " 2013 "
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\" & MonthName((Month(Date)) - 1) & "2013 payslip.pdf"
'.Display


.Attachments.Add "C:\Documents and Settings\SYSTEM2\Desktop\August 2013 Payslip.pdf" --> here also i want the code to display the previous month name as August 2013 Payslip.pdf
.Display






' ActiveWorkbook.Save
'ActiveWindow.Close
End With


End Sub

Thanks & Regards
Witson Joyet

 

[Andrew Poulsom]

Did you try?

emailaddress = Worksheets("Payslip").Range("C5").Value

You can use the same as you used for Filename in ExportAsFixedFormat for the name of the Attachment. You should add a .pdf extension in both cases.

 

[witsonjoyet]

I have tried it sir, but no use i didn't get the desired result. would you please help me?

 

[Andrew Poulsom]

What have you tried and what happens?

 

[witsonjoyet]

Dear Sir,

today you have suggested me to add the line as emailaddress = Worksheets("Payslip").Range("C5").Value, but i didn't get the result for this . actually the name is displayed in C5 cell in Payslip sheet . but for this name the mail id s are in mailing list sheet range B2:C59 . i want the mail id will be automatically updated in the TO address in the mail for the respective name . but i failed to do this. please help me to reslove this problem

the macro as follows:


Sub payslip()
'
' Payslip 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\" & MonthName((Month(Date)) - 1) & " 2013 Payslip", 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 & "Please find the attached salary slip for " & MonthName((Month(Date)) - 1) & " 2013 " & 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("mailing list").Select


emailaddy = Range("c5 )---> here i need the macro as automatically pick up the mail id


Set Outlookapp = CreateObject("Outlook.Application")
subj = "Pay slip for " & MonthName((Month(Date)) - 1) & " 2013 "
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\" & MonthName((Month(Date)) - 1) & "2013 payslip.pdf"
'.Display


.Attachments.Add "C:\Documents and Settings\SYSTEM2\Desktop\August 2013 Payslip.pdf" --> here also i want the code to display the previous month name as August 2013 Payslip.pdf
.Display






' ActiveWorkbook.Save
'ActiveWindow.Close
End With


End Sub

Thanks & Regards
Witson Joyet

 

[Andrew Poulsom]

Sorry, I misunderstood. How about?

emailaddress = Application.VLookup(Worksheets("Payslip").Range("C5").Value, Worksheets("mailing list").Range("B2:C59"), 2, False)