Automating Access, Excel, and Outlook

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

JackAndCoke

Thread Starter
Joined
Apr 26, 2007
Messages
152
I have a huge project that I need help on. Currently I run promotions on weekend events and track them via web form. The web form dumps into an Access database and I run a few queries on it (in one macro) then export the data to excel.

I update pivot tables, copy and paste them into an e-mail (in outlook) attach the excel file and send it off. Is there a way to automate all of this so that I can run a vba script or java script or something to do this on certain time increments? For instance, every hour, on the hour, run the macro, update pivot tables, save the excel sheet, create the e-mail, attach the file and send it to a distribution list?
 
Joined
Oct 20, 2004
Messages
7,837
A few things - if you are just sending the pivot tables, why not create them in Access and send directly from there?
There is VBA that will update your Excel sheet upon opening (closing, whatever) and also to automatically email.
As for the timing, I would suggest using Windows Scheduled Task manager to do that hourly work.
 

JackAndCoke

Thread Starter
Joined
Apr 26, 2007
Messages
152
So I've figured out how to automate everything except sending an e-mail with an attachment. Is there a way to automatically send an e-mail at of an excel file after it has been updated?
 

JackAndCoke

Thread Starter
Joined
Apr 26, 2007
Messages
152
so I have this code

Sub eMailActiveWorkbook()

Dim OL As Object
Dim EmailItem As Object
Dim Wb As Workbook

Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Wb = ActiveWorkbook
Wb.Save
With EmailItem
.Subject = "Webathon Reporting"
.Body = "Here is the Report"
.To = "[email protected]"
.Importance = olImportanceNormal 'Or olImprotanceHigh Or olImprotanceLow
.Attachments.Add Wb.FullName
.Send
End With

Application.ScreenUpdating = True

Set Wb = Nothing
Set OL = Nothing
Set EmailItem = Nothing


Application.Quit

End Sub
That I want to use at the end of a macro that e-mails out a file. Unfortunately, if it e-mails out the file and this macro is the Auto_Open, it will simply open the file, update all the pivot tables then e-mail it to that person and quit again.

What I need is a modified part of this code that turns off the Auto_Open before it mails out the file. Does this make sense?
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top