• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code for emailing payslips

johnlink

New Member
Hi I am rather new to VBA but have used Excel for some time. I have an excel file dump of employee payslips each week. All employees are contained in the one worksheet - structure of data is regular so a search for a key cell value eg "Earnings Summary" at the start of each payslip then create a range possibly using offset and an end marker at the next down "Earnings Summary" cell. From this selected range I then need to issue an email to the employee (email address is stored in another worksheet can be a vlookup) and attach a pdf of the payslip range selected. Following this I need the code to loop on down to the next payslip and do the same until the end of the file is reached. Needs to be fully automated if possible so can be done each payday by others. Also the worksheet is locked so may need to do a "save as" copy at the start of the code. Hope this makes sense and I look foward to any helpful replies from others who may have solved this. Many thanks. Johnlink.
 
OK thanks for guidance, I have decided to break down the task into steps. I am attaching the data dump file (all names changed for privacy). First step I need to have a macro loop through the file and find and delete all the data shaded yellow for ea employee per the yellow shaded example for 1st employee.
First yellow patch deletion can be a bunch of rows selected and deleted according to their position between the key marker cells - Earning Details A11 & Leave Accrued A27 (for 1st employee). Next yellow patch will need a range of cells selected and a CLEAR ALL exectued. Then the last yellow lot will be a set of rows to delete. Then cycle on to the next employee.
Thanks.
 

Attachments

  • PayRunAudit-Weekly-20190428.xlsx
    70.7 KB · Views: 36
johnlink
It would be better to write at once 'what do You need?'
to get as clear image as possible of Your challenge
and
Where are those needed 'outputs'?
 
OK 'what do I need' -
1. need code to find and to delete all the fields marked yellow in the worksheet (for each employee)
2. then need code to select each employee payslip record ie all remaining cells, and send a pdf via email to their stored email address at top of their record row for each employee.
 
OK Fluff I was not aware that was a restriction (or forum etiquette), just new here and rushed to get this solution working. Lucky no one has taken their valuable time to post anything back to me re a solution on this as yet - just flaming from the forum vigilantes.
 
johnlink
You also should follow same rules as everybody.
Have You seen below text anywhere?
59941
Have You read that?

You also wrote something else to someone ... who has been here about three years ago...
If You need help then You should help Yourself to give answers to make possible to try to help You.
 
Back
Top