Please help me get time back with my kids.

[Vampyredh]

Hello all,

I am new to the wonderful world of visual basic, and I have been having some issues trying to get something to work for me and was hoping on of you may be able to assist me. I got into Visual Basic because I do a lot of emailing based on an excel report I get daily from my company. The report contains many rows of assignments that are due.

I have to read this report check dates in column E against today's date - 5 days, so if the date in column E is less than 5 days from the current date, I have check column F to see if there is an #N/A, if there is I am to send the Person involved in an email. There name can be found in column G. The email is to contain the id number and company name in the subject line which the id can be found in column A and the company in column Z. I have tried numerous forums and threads over the last week looking for help. I am generating 30 to 40 emails per day based on this excel spread sheet. I know that automation is the answer to saving me many hours (I am paid a pittance for pay working 60 plus hours a week 20 of it is spent sending this email) I have included a base example of the workbook no formatting mostly for a visual reference. If any of you could help me out with a visual basic macro to do this for me I would be very humbled as my kids would get to see me a little more. I would even be fine if it created a new word doc that would have this info that I need if an email is not possible.

 

[Eric W]

Mark, thank you for testing the code, and for the suggestion about how to find the last row. That is an earlier version though, there are a couple changes.

Vampyredh, did you figure out how to print a screenshot, or did you try the debugging?

 

[Vampyredh]

I did try running it. When it hits the create outlook message section it does nothing at all, the macro now runs but nothing is being generated in outlook at all

 

[MARK858]

That is an earlier version though, there are a couple changes.

The code in post #9 works as well

Vampyedh
I did try running it. When it hits the create outlook message section it does nothing at all, the macro now runs but nothing is being generated in outlook at all

Go to a free file hosting site like www.box.com upload a copy of your file (sensitive data sanitised), mark it for sharing and post the link it provides in the thread.

 

[Vampyredh]

https://www.dropbox.com/s/f5zw4zcz7zdw4ae/emailproblem.xlsx?dl=0

Dropbox of a blank sheet Set up. No data other than what I am looking for.

 

[MARK858]

Your date is 29th February 2016 which is more than 5 days from today's date.

less than 5 days

 

[Vampyredh]

<article>

Ok so they liked what I was doing so much they changed it up on me and I am more lost now.

They want me to pull the OpID from Column A if Column C has and #N/A And create a new email, That email will have column b plus @xxx.com

The Subject Line will be Coulmn A and Column M

And the body will have Column A and M

And then send.
This will then update column E to say yes

And enter the date of the email in Column G

Can any of you help me fix the issue. You did such a wonderful Job helping me before. I hope it would not be to much to ask for your help again. I have included the new updated Excel sheet. And am hoping you can assist me to get this accomplished. Your code worked great for my last issue but it does not work for this issue and I can not fix it.

https://www.dropbox.com/s/brlik24m2hpbr3z/EOM.xlsx?dl=0
​

</article>