Convert Sheet to PDF and Send by email

[bkutkut]

Hello,

I'm a school teacher trying to make it easy to create students' reports, convert them to PDF and send by email. In my excel workbook, I have two sheets. The first one is "Students Info" that has students' numbers, names and emails. The second one is "Students Reports". In the second sheet, each one of the students has a report. Is there a macro that can convert each one of the reports to a PDF file and send to student's email as per the emails list in the students Info sheet?

Thanks!

Bassam

[bkutkut]

Here's my workbook attached.

Please, I need your help.

[Parth007]

Hibkutkut, I have a code which will convert your report sheet to PDF & send mail ...

Please Login or Register  to view this content.

You have to specify the mail ID's in the code...The code will convert entire sheet into One PDF & mail it directly.. you can change the code accordingly...

I am not aware how do we send the mails & reports in specific to "Student Info" sheet..

[bkutkut]

Hello,

Many thanks for your help! I really appreciate it! Now I have hope that this work may come true. Maybe I wasn't clear enough. Each one of the students in the first sheet should receive his report as PDF; ONLY his report, not the entire sheet.
This report is to be sent to the student's email specified in the list on the "Students Info" sheet. That means if I have 10 students on my list, then 10 PDF reports will be generated and each report will be sent to the student's email as an attachment.

I know it is challenging but it will help us a lot.

Thanks again!

Bassam

[jaslake]

Hi Bassam

I've added this Code to the Workbook to send an Individual Email to each Student.

Please Login or Register  to view this content.

I've slightly modified Part007's Code to accommodate the same. The Email Code is set to Display...change to Send here...

Please Login or Register  to view this content.

Click the Button.

[bkutkut]

Hello John,

Awesome! It is working nicely! I really don't have any experience in VBA coding. I wonder if you can do me a favor.
1. On the list of students, there are 10 students. Your code does the job nicely for only 10 students. But sometimes we have more than this number. Sometimes we have more than 30. Is there a way to make it as per the number of students on the list?
2. Can you make a change on the way the email is sent so that it sends all the emails directly without displaying the email?

Thanks a million!

Bassam

[jaslake]

Hi Bassam

This'll take a bit...I'll get back to you.

On the list of students, there are 10 students. Your code does the job nicely for only 10 students. But sometimes we have more than this number. Sometimes we have more than 30. Is there a way to make it as per the number of students on the list?

[bkutkut]

No problem. Thanks!

[jaslake]

Hi Bassam

We have a couple of options for dealing with the number of Students. What I'd really like is to get an understanding of what your Process is going to be...how do you (the Users) intend to use this.

This is my vision of the Process:

• User creates a Report Template for each Student on the list in Reports Sheet.
• User fills in the "blanks" on each Students Report Template.
• User Clicks the "Send Mail" Button.

What's your Vision?

[bkutkut]

Hi jaslake,

Thanks for your response! On the Reports sheet, there are 35 templates (which is the maximum number of students we can have in a class). However, the number of students is sometimes less than that. So not all the report templates are used. On the "Students Info" sheet, we fill in students numbers, names and emails. Then we fill in the blanks on the reports. As I mentioned, we don't fill in all the 35 report templates; only as per the list of students between 1 and 35. After that, we have to convert each report to PDF and send it to student by email.

I hope this helps.

Thanks again!

Bassam

[jaslake]

Hi Bassam

The Code in the attached has been modified such that it creates a Report Template for each Student ID in Sheet Students Info...if you have 5 Students, 5 Templates are created...if 15, 15 Templates are created, etc..

It is a four step process:

1. Enter Student Info
2. Click Create Reports
3. Fill in the "Blanks" in Student Report
4. Click Send Mail

To accomplish this change the Code in Module 1 as indicated.

Can you make a change on the way the email is sent so that it sends all the emails directly without displaying the email?

Please Login or Register  to view this content.

Let me know of issues.

[bkutkut]

Hi,

Your new idea is great, but it will not work for me for some reasons. Can we stay on the previous code? It's much better for me. I tried to apply the codes on my main workbook, but I failed. Can you just apply the previous codes on the attached workbook? The students Info sheet has the students names. IDs and emails. There are two more sheets for reports; Midterm Reports and Final Reports. Both of them will use the same list of students on the Students Info sheet. If you open the Midterm Reports sheet, you will see a button "Send Midterm Reports". I would like to assign it to your code to send the midterm reports. You will also find the same thing on the Final Reports sheet. That's because midterm reports and final reports will be sent at different times.

I know I am asking for much and I am sorry for that and thanks for your help!

Bassam

[jaslake]

Hi Bassam

This is a bit of a rewrite...it'll take me a while.

[bkutkut]

Thanks! Awaiting.

[jaslake]

Hi Bassam

In the attached the Code has been rewritten to reflect the Structure of your Actual File and to deal with Midterm and Final Reports.

Please note this Code in the ThisWorkbook Module...

Please Login or Register  to view this content.

Also, please note I've modified the Formulas in the Student Number Fields of Midterm (Column I) and Final (Column J). Note further ALL Formulas in Final Reports have been converted to Absolute References.

The Send Reports Code takes a bit to run, about 45 seconds, due to the number of Templates that exist in the Worksheets (Midterm and Final). If your using Excel 2010 or later this time could possibly be reduced. This line of Code is what takes time as it involves printer communication.

Please Login or Register  to view this content.

As previously noted, to send the Emails directly, change Display to Send in each of the Mail Modules. Let me know of issues.

[bkutkut]

Hello,

When I click on the "Send Midterm Reports" button, it gives me the following error message "Run-time error 1004. Method name of object_Worksheet failed". Then, the whole workbook freezes.

Final reports work like magic! Exactly as I wanted. However:
1. If there are two similar names in the students list, it gives me an error message and stops working. In my case, it is possible to have similar names in the students list.
2. I tried to fill up all the students names, numbers and emails (35). Then, I clicked on the "Send Final Reports" button. Only 34 reports were sent out. Report 35 was not sent. I tried this many times and I got the same result.
3. When all emails are sent, I don't get the message "Email has been sent successfully".

I really appreciate your help and patience. Many thanks!

Bassam

[bkutkut]

Hello,

When I click on the "Send Midterm Reports" button, it gives me the following error message "Run-time error 1004. Method name of object_Worksheet failed". Then, the whole workbook freezes.

Final reports work like magic! Exactly as I wanted. However:
1. If there are two similar names in the students list, it gives me an error message and stops working. In my case, it is possible to have similar names in the students list.
2. I tried to fill up all the students names, numbers and emails (35). Then, I clicked on the "Send Final Reports" button. Only 34 reports were sent out. Report 35 was not sent. I tried this many times and I got the same result.
3. When all emails are sent, I don't get the message "Email has been sent successfully".

I really appreciate your help and patience. Many thanks!

Bassam

[jaslake]

Hi Bassam

Please attach the File that exhibits ALL these issues.

[bkutkut]

Hi,

The file is attached.

Bassam

[jaslake]

Hi Bassam

It would appear the Page Breaks were off a skosh in each Report Sheet. I've reintroduced the Fix Page Break Code and tested...appears to work.

Regarding this issue...

If there are two similar names in the students list, it gives me an error message and stops working. In my case, it is possible to have similar names in the students list.

The Code has been revised to name the New Worksheets (nSheet) to the Name plus ID. For example "Tom 20151111" as I assume Student ID's will be unique.

Have to be honest with you Bassam, I'll not test this with sending 35 Emails...don't have the patience for that...that'll be your job. The Code is set to Send.

Run the Code with the Using Timer Button. You WILL SEE "not responding"...let Excel do it's thing...now, if it's running for more than 4 or 5 minutes something's amiss.

When your finished testing with the Using Timer Buttons, simply delete them and uncomment the MsgBox ("Emails have been Sent Successfully") at the bottom of each Email Code.

Let me know of issues.

Edit:
On my platform Midterms takes about 86 seconds to complete
On my platform Finals takes about 103 seconds to complete.

[bkutkut]

Hi,

Everything went well this time. Thanks, man!

Bassam

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.