Hi!
I have a nice little challenge for you's (ok maybe it's not a challenge for you but it is for me!
).
I'd like to create a code that will 0) will go look if there is a certain value in a column cell, 1) if there is a 'Yes' in that column, copy values from certain cells to blank merged cells in another tab, 2) save second tab as PDF using a cell value as title, and 3) send PDF as mail attachment with a cell value as email address, 4) clear merged cells, and 5) enter "Yes" in a specific column of the first tab. Is this possible?
Here is the context:
The first tab has raw information from a form that was completed and submitted. The second tab is a template for a receipt with blank, merged cells where information will be copied. I've added a column at the end of the first tab, titled 'Receipt sent?'. This is because new raw information will be added weekly. So the idea is that whenever the user goes in the sheet, pastes the new information, they can press a button at the top that will run the macro, and the macro will go and do steps 1-5 above for all rows that do NOT have 'Yes' in the last column.
Once a macro is done, I would like it to go look for the next row that has a value in column A, but does not have the 'Yes' in the last column. When it has completed all rows, to prop the message 'Receipts have been created for all new entries'.
Here are the details:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]original tab ('Raw')[/TD]
[TD]to be coped in receipt tab ('Receipt)[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]B2 +[/TD]
[TD]B13 (merged cells)[/TD]
[/TR]
[TR]
[TD]Address[/TD]
[TD]E2 +[/TD]
[TD]B14 (merged cells)[/TD]
[/TR]
[TR]
[TD]Lot[/TD]
[TD]G2 +[/TD]
[TD]B19 (merged cells)[/TD]
[/TR]
[TR]
[TD]Service[/TD]
[TD]H2 +[/TD]
[TD]B23 (merged cells)[/TD]
[/TR]
[TR]
[TD]Days[/TD]
[TD]I2 +[/TD]
[TD]B27 (merged cells)[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]L2 +[/TD]
[TD]E29 (non-merged cell)[/TD]
[/TR]
[TR]
[TD]Payment[/TD]
[TD]F2 +[/TD]
[TD]H29 (non-merged cell)[/TD]
[/TR]
</tbody>[/TABLE]
Title of the PDF document: value in column B of the row
Email address to include in email message: value in D of the row
MAIL: if it can be done directly via gmail that would be amazing. If not, Outlook.
BONUS: if there is a way we can also include a message in the VBA code so that when the email is generated, we have the email address in the 'TO' field, we have a generic message prepared, and the PDF as attachment?
OPTION: some people have not provided an email address, which in that case the value is exactly "n/a". In that case, the macro can skip generating the email. or it can simply ignore and we will manually close the email and send physical mail to these recipients. whatever is easiest.
I have before created a macro to copy values to another sheet and then clear the cells, but I was not dealing with some merged cells, and I wasn't generating PDF or sending email. Your help is very very appreciated!
even if it's parts of the solution (only PDF, only mail, only code for merged cell, etc.). I can piece it together afterwards.
Thanks in advance!!
PS I hope this makes sense. Please ask if there are unclear items!
Manon
I have a nice little challenge for you's (ok maybe it's not a challenge for you but it is for me!
). I'd like to create a code that will 0) will go look if there is a certain value in a column cell, 1) if there is a 'Yes' in that column, copy values from certain cells to blank merged cells in another tab, 2) save second tab as PDF using a cell value as title, and 3) send PDF as mail attachment with a cell value as email address, 4) clear merged cells, and 5) enter "Yes" in a specific column of the first tab. Is this possible?
Here is the context:
The first tab has raw information from a form that was completed and submitted. The second tab is a template for a receipt with blank, merged cells where information will be copied. I've added a column at the end of the first tab, titled 'Receipt sent?'. This is because new raw information will be added weekly. So the idea is that whenever the user goes in the sheet, pastes the new information, they can press a button at the top that will run the macro, and the macro will go and do steps 1-5 above for all rows that do NOT have 'Yes' in the last column.
Once a macro is done, I would like it to go look for the next row that has a value in column A, but does not have the 'Yes' in the last column. When it has completed all rows, to prop the message 'Receipts have been created for all new entries'.
Here are the details:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]original tab ('Raw')[/TD]
[TD]to be coped in receipt tab ('Receipt)[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]B2 +[/TD]
[TD]B13 (merged cells)[/TD]
[/TR]
[TR]
[TD]Address[/TD]
[TD]E2 +[/TD]
[TD]B14 (merged cells)[/TD]
[/TR]
[TR]
[TD]Lot[/TD]
[TD]G2 +[/TD]
[TD]B19 (merged cells)[/TD]
[/TR]
[TR]
[TD]Service[/TD]
[TD]H2 +[/TD]
[TD]B23 (merged cells)[/TD]
[/TR]
[TR]
[TD]Days[/TD]
[TD]I2 +[/TD]
[TD]B27 (merged cells)[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]L2 +[/TD]
[TD]E29 (non-merged cell)[/TD]
[/TR]
[TR]
[TD]Payment[/TD]
[TD]F2 +[/TD]
[TD]H29 (non-merged cell)[/TD]
[/TR]
</tbody>[/TABLE]
Title of the PDF document: value in column B of the row
Email address to include in email message: value in D of the row
MAIL: if it can be done directly via gmail that would be amazing. If not, Outlook.
BONUS: if there is a way we can also include a message in the VBA code so that when the email is generated, we have the email address in the 'TO' field, we have a generic message prepared, and the PDF as attachment?
OPTION: some people have not provided an email address, which in that case the value is exactly "n/a". In that case, the macro can skip generating the email. or it can simply ignore and we will manually close the email and send physical mail to these recipients. whatever is easiest.
I have before created a macro to copy values to another sheet and then clear the cells, but I was not dealing with some merged cells, and I wasn't generating PDF or sending email. Your help is very very appreciated!
even if it's parts of the solution (only PDF, only mail, only code for merged cell, etc.). I can piece it together afterwards. Thanks in advance!!
PS I hope this makes sense. Please ask if there are unclear items!
Manon
