musicman715
New Member
- Joined
- Jul 15, 2011
- Messages
- 45
Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
I am working on a macro to that emails my clients one week before their invoice is due. The first part of my code opens a data sheet called “Invoice Email Data Sheet” and copies info from the Excel Invoice over to specific columns in the “Invoice Email Data Sheet”. However, I cannot record macro that records my actions in our accounting software, so I cannot automatically get this info to copy over from our accounting software to the correct column in excel 2010. I do not have IT clearance to download a whole computer macro program in order to achieve this copying and pasting over from our accounting software automatically. So I can and have been doing that copying and pasting manually to get the email address copied from our accounting software over to my excel data sheet.<o></o>
1. Is there a way to do this exclusively with excel VBA, or do I have to have a whole computer macro program to record the clicks, and copy actions in our accounting software?<o></o>
Here are the column headings in the “Invoice email data sheet”. A is Invoice #, B is Event Name, C is Invoice Date, D is Due Date, E is Invoice Amount, F is Contact Email, and H4 is Today’s Date (I put it in H4 because if I put it in row 2 with all the rest of my first row of data and heading in row 1, it messes up when I run the macro to copy over the info from the invoice.)<o></o>
Here’s a slice of code to copy and paste the info over from the Invoice to the “Invoice Email data sheet”<o></o>
Range("AA8:AE8").Select ‘this is a range from the actual invoice<o></o>
Selection.Copy<o></o>
Windows("Invoice Email Data Sheet.xlsx").Activate<o></o>
Range("A2").Select<o></o>
ActiveSheet.Paste<o></o>
2. What is the code instead of Range (“A2”).Select to make excel select the next open row in column A so that the Invoice # that’s already in cell “A2” is not overwritten, and so that I each time the macro runs it selects the next open cell (without writing already in there) in the appropriate column<o></o>
Once I’ve copied all the information over to the “Invoice Email Data Sheet”:<o></o>
3. What is the code to say, execute next section of code only on ‘Enter’ keypress from the last non-blank cell in column F (This is where my email addresses will be.)<o></o>
4. The final code I need is to set up an email to be sent from Microsoft outlook 1 week prior to the due date in Column D to the “Contact Email” in column F that says,<o></o>
“To Whom it may concern,<o></o>
Your Invoice (# from last open cell in column A) for (Last open cell in column B…event name and number) dated (Column C), for (Amount in column E) is due in 1 week on (Due Date in column D)<o></o>
Thank You for your business,”<o></o>
I want the information in parentheses to be automatically pulled from my data sheet.<o></o>
Here’s the hitch…I want it to go out 1 week prior to the due date from column D, and I’ll be adding new info to the data sheet each time I create an invoice so that another email can be sent for each invoice I create and I don’t want excel to get confused as to which row it is pulling data from when it sets up the email to be sent from outlook, and creates the email in outlook.<o></o>
Any help is greatly appreciated. If you have a response to a particular question number, please label it, so I know which question the answer goes with.<o></o>
Thanks,<o></o>
Musicman715<o></o>
fficeffice" /><o></o>I am working on a macro to that emails my clients one week before their invoice is due. The first part of my code opens a data sheet called “Invoice Email Data Sheet” and copies info from the Excel Invoice over to specific columns in the “Invoice Email Data Sheet”. However, I cannot record macro that records my actions in our accounting software, so I cannot automatically get this info to copy over from our accounting software to the correct column in excel 2010. I do not have IT clearance to download a whole computer macro program in order to achieve this copying and pasting over from our accounting software automatically. So I can and have been doing that copying and pasting manually to get the email address copied from our accounting software over to my excel data sheet.<o
></o>1. Is there a way to do this exclusively with excel VBA, or do I have to have a whole computer macro program to record the clicks, and copy actions in our accounting software?<o
></o>Here are the column headings in the “Invoice email data sheet”. A is Invoice #, B is Event Name, C is Invoice Date, D is Due Date, E is Invoice Amount, F is Contact Email, and H4 is Today’s Date (I put it in H4 because if I put it in row 2 with all the rest of my first row of data and heading in row 1, it messes up when I run the macro to copy over the info from the invoice.)<o
></o>Here’s a slice of code to copy and paste the info over from the Invoice to the “Invoice Email data sheet”<o
></o>Range("AA8:AE8").Select ‘this is a range from the actual invoice<o
></o>Selection.Copy<o
></o>Windows("Invoice Email Data Sheet.xlsx").Activate<o
></o>Range("A2").Select<o
></o>ActiveSheet.Paste<o
></o>2. What is the code instead of Range (“A2”).Select to make excel select the next open row in column A so that the Invoice # that’s already in cell “A2” is not overwritten, and so that I each time the macro runs it selects the next open cell (without writing already in there) in the appropriate column<o
></o>Once I’ve copied all the information over to the “Invoice Email Data Sheet”:<o
></o>3. What is the code to say, execute next section of code only on ‘Enter’ keypress from the last non-blank cell in column F (This is where my email addresses will be.)<o
></o>4. The final code I need is to set up an email to be sent from Microsoft outlook 1 week prior to the due date in Column D to the “Contact Email” in column F that says,<o
></o>“To Whom it may concern,<o
></o>Your Invoice (# from last open cell in column A) for (Last open cell in column B…event name and number) dated (Column C), for (Amount in column E) is due in 1 week on (Due Date in column D)<o
></o>Thank You for your business,”<o
></o>I want the information in parentheses to be automatically pulled from my data sheet.<o
></o>Here’s the hitch…I want it to go out 1 week prior to the due date from column D, and I’ll be adding new info to the data sheet each time I create an invoice so that another email can be sent for each invoice I create and I don’t want excel to get confused as to which row it is pulling data from when it sets up the email to be sent from outlook, and creates the email in outlook.<o
></o>Any help is greatly appreciated. If you have a response to a particular question number, please label it, so I know which question the answer goes with.<o
></o>Thanks,<o
></o>Musicman715<o
></o>
