Thread: Updating data,attaching,and e mailing

Dear Forum,
I am stuck with combining the different things at a place ..Attached is the sheet that explains it in a better way ..
1/Update Record:
update the record from the data base placed on the drive (same computer)
Eg: As location keeps changing get the updated location from the master sheet placed on a different drive but on the same computer.
2/ Creat an e mail with
a/ Details of tables from sheets mentioned in column G, should be copied and aligned in the body of the e mail
b/ Attach same sheet as an excel sheet to the e mail (attachment).Eg : Attach sheet"a" to e mail going out to Jim.
c/ Attach a PDF document placed on the desktop to the e mail,this document is different for different e mails
Eg:Jim will have a different document than Basu ,so the document for jim has to be attached to the Email going out to Jim
d/ Send it in case the condition is "A" in the last column.
Query
Can we creat a combo/list box with all these possibilities in to execute this .
Thanks in Advance ..

Hi N

Regarding this

1/Update Record:
update the record from the data base placed on the drive (same computer)
Eg: As location keeps changing get the updated location from the master sheet placed on a different drive but on the same computer

Update what in the record? Column H (the location of the PDF) or other parts of the record? From what Master Sheet? I have no idea what your referring to in this requirement...sorry.

Regarding this

a/ Details of tables from sheets mentioned in column G, should be copied and aligned in the body of the e mail

The cells highlighted in the sheets a, b and c are not alligned...why not? The sheet itself (or a range from the sheet) can be inserted into the email body but it'll look just as it is, aligned or not. The same sheet can be attached to the email but it'll be what it is, aligned or not.

Regarding this

c/ Attach a PDF document placed on the desktop to the e mail,this document is different for different e mails
Eg:Jim will have a different document than Basu ,so the document for jim has to be attached to the Email going out to Jim
d/ Send it in case the condition is "A" in the last column.

I believe the code I previously provided allows you to select the PDF to attach. Sending it based on condition "A" is no problem.

Regarding this

Can we creat a combo/list box with all these possibilities in to execute this

Don't know cause I don't understand the requirements.

Get back to me.

Update the Location of a person as he keeps on going from one city to another ,in Sheet1, This data will be copied from a master work book placed in say drive"D:\Documents and Settings\nutty\Desktop".
2/ Alignment has been sorted out in a b and c, I can do it manually as there are only 25-30 sheets to work on ,so we can overstep that part .
3/ Yes your code allows to attach the PDF wonderfully well but I am sending it as per condition A is what I am looking at .
4/ i was referring to the box in you code that you used ...Is it possible to make the one for my request as well ..
Regards

Hi N

If you wish the procedure to do the updating of the location and you want me to test it, you'll need to attach a file that's representative of what the Master Workbook looks like (column layout, etc). Populate the file with information that relates to the persons you have in Sheet1 of ncp1.xls.

While your at it, revise your ncp1.xls such that demonstrates how the Alignment issue is resolved (sheets a, b & c). The procedure will assume the ranges have been aligned. I want to see it.

Regarding this

4/ i was referring to the box in you code that you used ...Is it possible to make the one for my request as well

Don't know yet...I'll need the above files to play with to see how it all fits together.

Dear J,
I did some editing thats all ,points to remember:
1/ Data in the Master sheet changes frquently ,so it should get auto updated in "Sheet1".
Regards

Damn ...Office ...I missed attaching the files I guess!!

Hi N

******* Sorry...I was responding as your post came in. I'll look at your attachments. Thanks N *****

Regarding this

I did some editing thats all

Yes, I know...you previously explained such too me. I'd like to see it. Show me. The procedure will assume the ranges have been aligned. I want to see it.

Regarding this

Data in the Master sheet changes frquently ,so it should get auto updated in "Sheet1".

I understand that. If you know how to write the code to update Sheet1 from the Master, please do so and I won't concern myself with the issue. However, if you wish me to write the code then

If you wish the procedure to do the updating of the location and you want me to test it, you'll need to attach a file that's representative of what the Master Workbook looks like (column layout, etc). Populate the file with information that relates to the persons you have in Sheet1 of ncp1.xls.

Please see my previous post.

Sry for being so clumsy ..I have recorded this macro and it updates my records ..But this i use as a stand alone function ...
Regards

Hi N

In the code in the attached, you'll need to change the Path Reference in three places to suit your environment:

1. In the UserForm Initialize Event to set the Path to where your PDF's live
2. In the DoMail Procedure to set the Path to where your Signature lives.
3. In the Update_Location procedure to set the Path to where your Master File lives.

In the Master file, your Names had a trailing space, eg "Jim ". If your actual Master file has this trailing space, you'll need to get rid of it.

Let me know of issues.