Possible to use excel to trigger sending of mails??

[borderfox]

This may be way outside the realms of possibility with excel - but I guess there's no harm in asking!

If we have a list of club members who renew their subscriptions with us each month, is it possible for excel to trigger the sending of mails to them prior to subscription expiry?

Current setup is that we have members details entered into an excel sheet - with one field for sub expiry dates. We then manually send mails to each of them - reminding them to renew. These mails are copied and pasted from a template. The template is edited to include their membership number. Then mail is sent via gmail.


Is it even remotely possible to automate this?
Any thoughts on the subject would be very welcome.

[Lifesigns]

Hi borderfox,

I've been wondering about this for a while but not taken the time to have a really good look. I have macros I use daily that copy excel reports for email but only after I have run them. From what I understand you are rather limited when it comes to outlook as many things are blocked to prevent rogue emails being sent without your knowledge.

A really good resource for this is http://www.rondebruin.nl/sendmail.htm I've used some code from there before.

Regards,

Lifesigns

[borderfox]

Hi Lifesigns. Thanks for taking the time out to post. I guess that the time invested in trying to find a solution to this and implement it isn't worth it (in my case) at the moment - as it looks like something that's far from straightforward to achieve. Is there a proprietary software programme that could achieve this other than excel?

[Lifesigns]

Hi borderfox,

Me again...

Like I said I had been wondering about it and this morning gave it a go. The code isn't specific to Excel it's VBA so you could probably use it in any Office product that supported VBA. That said this thread might be more relevant to the VBA section of the forum.

Anyway I've tested it this morning on our MS Exchange server and it worked a treat, this is something I'll definitely use in future. I note you mentioned it's Gmail you want to use - it should work with that as well.

I'm not sure if you've used VBA before if not then it's always good to start it makes things much easier.

Here is the code... it should go in a standard module in the workbook that has the subscription information.

Please Login or Register  to view this content.

There were various fragments on the internet but I collated them into this macro and added some variables and comments so it makes a little more sense.

Once you have tested it and confirmed it works then I'd say you can make a few small changes to trigger the code and off goes your email.

Perhaps create a new worksheet called template and create the template text you want sent from there. You can then alter the macro to pull in the customer's name from the worksheet with the subscription info and add that to the template text.

The only thing I have a concern with is the fact that the account password is visible to everyone who knows how to bring up the VBA editor. I was thinking in my setup I would create a worksheet for that with the username and password and ensure that it’s always veryhidden then use named ranges for both. The macro above can fetch them from the hidden sheet which should be more secure. I would also lock the project from viewing.

As long as the workbook is open you can add a button so when they are ready it can check each sub expiry date and if required send an email.

Thanks for posting this question, I should have looked at this a while ago.

Hope it works for you.

Regards,

Lifesigns

[Lifesigns]

Hi Lifesigns. Thanks for taking the time out to post. I guess that the time invested in trying to find a solution to this and implement it isn't worth it (in my case) at the moment - as it looks like something that's far from straightforward to achieve. Is there a proprietary software programme that could achieve this other than excel?

I never seen your reply before posting...

If you aren't too familiar with Excel then I could see how this may seem like a huge task but the reward comes once you have it set and working.

When I started this job I found 90% of the reports were produced manually - repeating the same mundane thing over and over - not only very boring but also prone to human error. One in particular took me almost an hour a day even longer on Mondays for a weekly version and then month end was a nightmare.

I took the time to redesign the entire thing and reduced it from 7 seperate workbooks to just 1 with the same information much clearer. It's all based on macros and now only takes 2 min to update each morning.

Let me know how you get along, I'd be more than willing to help out where I can.

Regards,

Lifesigns

[romperstomper]

Just FYI: when you write this:

Please Login or Register  to view this content.

only the last variable is a string - all the others are Variants. You need to specify the type for each one in VBA.

[Lifesigns]

Just FYI: when you write this:

Please Login or Register  to view this content.

only the last variable is a string - all the others are Variants. You need to specify the type for each one in VBA.

That's right yea! Thanks for pointing it out.

[mrsrinivas1988]

Hi all,
I have a problem in extracting latest can anyone help me

I have a sheet with some coloumns and many rows
where Iam having different entrys in cells
Now my work is to count the no.of rows having
"A" in D coloumn and
"DAD" in N Coloumn

I got this count using formula =SUM((D3:D20="A ")*(ISNUMBER(SEARCH("DAD ",N3:N20))))

But now the problem is in Coloumn K Iam Having Date I want Latest date for above filtered data Can anyone pls Help me

Attached Excel Will give u Clear idea of my Requirement

[romperstomper]

Welcome to the forum, but:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.