By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,339 Members | 1,693 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,339 IT Pros & Developers. It's quick & easy.

Code/macro to email all tables in DB?

P: 21
Is there a way that i can (with the simple click of a button) export all the tables in a access database into a single .xls file and attach it to an email (in outlook)?
It feels far fetched, but since its all withing MS office was wondering if it was possible)
Thanks!
Jul 20 '10 #1
Share this Question
Share on Google+
16 Replies


Delerna
Expert 100+
P: 1,134
The short answer is YES...Using VBA
The long answer is WHY ?

Also, since you ask this question, are you aware that you can link an xls spreadsheet into access as a linked table?

What this means is that you can add,update and delete the records in the linked table/xls document using access.

You can then open the spreadsheet again in excel and all the additions, updates and deletions you made in access will also appear in excel. This is also true the other way. Make changes to it using excel and then open it in access and your changes appear there.

The excel document is effectively a single object that can be used by both excel and access


So as an extra method besides VBA to achieve your question.
Expand|Select|Wrap|Line Numbers
  1. 1) You could link an excel spreadsheet into access
  2. 2) When the button is clicked
  3.    a) run a delete query that deletes all 
  4.       records from the linked spreadsheet
  5.    b) run an insert query that selects all
  6.       records from a table and inserts them
  7.       into the linked excel table
  8.  
Now you can email the excel document containing the inserted table contents in anyway you wish
Jul 21 '10 #2

P: 21
Thanks Delerna,
I got the exporting part working, but its more the emailing than I want to get figured out.
Right now I have a macro that exports the tables into respective .xls files (or even one single .xls file). But the problem with the single .xls file is that importing the multiple tables from it is srewed up. and its too late to change the architecture of the DB since all the forms querries etc have been created out of the tables...
Jul 21 '10 #3

Delerna
Expert 100+
P: 1,134
Ok, perhaps this code will help

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSendMail_Click()
  2.    Dim OlApp As Outlook.Application, oMail As MailItem
  3.    Set OlApp = CreateObject("Outlook.Application")
  4.    Set oMail = OlApp.CreateItem(olMailItem)
  5.    oMail.To = "TheEmailAddress"
  6.    oMail.Subject = "TheSubject"
  7.    oMail.Attachments = "TheFileToAttach.xls"
  8.    oMail.Body = "Here it is"
  9.    oMail.Send
  10.    Set oMail = Nothing
  11.    Set OlApp = Nothing
  12. End Sub
  13.  
You will need to goto Tools/References from the VBA code window for your form and tick
Microsoft Outlook 10.0 Object Library
before writing the above code

The above code will use the users outlook email account to send the email from.
Jul 21 '10 #4

P: 21
I just tried it, but I click the button but nothing happen.
Also i want to attach multiple .xls files. (when i tri doing this through a macro, it creates individual emails for each attachment)
Jul 21 '10 #5

Delerna
Expert 100+
P: 1,134
Multiple attachments
Expand|Select|Wrap|Line Numbers
  1. oMail.Attachments = "TheFileToAttach.xls;" & "TheOtherFileToAttach.xls;" & "AThirdFileToAttach.xls;" 
  2.  
Multiple recipients
Expand|Select|Wrap|Line Numbers
  1. oMail.To = "TheEmailAddress;AnotherEmailAddress;YetAnother" 
  2.  


Did you check the email for the person you sent it to;
Did you check your outbox in outlook to see if they got there?

If not
Breakpoint each line of code and then run it so you can follow what is happening
Jul 21 '10 #6

Delerna
Expert 100+
P: 1,134
PS the same code works for me so.....?
Jul 21 '10 #7

P: 21
it hasnt eached the recipient> not even the outbox of the sender(me).
The exact code i tried out.
Expand|Select|Wrap|Line Numbers
  1. Private Sub email1_Click()
  2.    Dim OlApp As Outlook.Application, oMail As MailItem
  3.    Set OlApp = CreateObject("Outlook.Application")
  4.    Set oMail = OlApp.CreateItem(olMailItem)
  5.    oMail.to = "dileshXXX@hotmail.com"
  6.    oMail.Subject = "test Auto email"
  7.    oMail.Attachments = "D:\Database\tbl_PO.xls"
  8.    oMail.Body = "Sending table via autpmail. Here it is"
  9.    oMail.Send
  10.    Set oMail = Nothing
  11.    Set OlApp = Nothing
  12. End Sub
  13.  
I have ticked the outlook ref library.
I have also have Outlook open and connected to my exchange server.
Jul 21 '10 #8

Delerna
Expert 100+
P: 1,134
Sorry My code wasn't identical, I had the attachment part remmed out and it is not correct.

Try this
Expand|Select|Wrap|Line Numbers
  1. Private Sub email1_Click()
  2.    Dim OlApp As Outlook.Application, oMail As MailItem
  3.    Set OlApp = CreateObject("Outlook.Application")
  4.    Set oMail = OlApp.CreateItem(olMailItem)
  5.    oMail.To = "dileshXXX@hotmail.com"
  6.    oMail.To = "graham.taylor@btequipment.com.au"
  7.    oMail.Subject = "test Auto email"
  8.  
  9.    Mail.Attachments.Add "D:\\Database\tbl_PO.xlso"
  10.    'Mail.Attachments.Add "One Of These For Each Attachment
  11.  
  12.    oMail.Body = "Sending table via autpmail. Here it is"
  13.    oMail.Send
  14.    Set oMail = Nothing
  15.    Set OlApp = Nothing
  16. End Sub
  17.  
Jul 22 '10 #9

P: 21
hey delerna,
its still very much the same. Its just that the button doesnt start outlook (or send the mail to the "outbox")
:(
Jul 22 '10 #10

Delerna
Expert 100+
P: 1,134
then maybe outlook security settings are preventing emails from VBA.

I have an addin installed that lets me tell outlook to allow scripted emails from a particular application either this time only or allways allow.
Jul 23 '10 #11

P: 21
I'm now tring the 'sendObject' macro. It neatly opens outlook and attaches the file. to the email. But if It cant send more than one file attached in a single email. If i add 2 actions to attach two files, it opens 2 separate emails with the respective files...
Maybe there's a way for me to refine this method instead?
Jul 23 '10 #12

Delerna
Expert 100+
P: 1,134
Another option, one I use most of the time, is to use CDO which doesn't use outlook
or any other user email client at all

Here is some VB script if you want to play with it
Expand|Select|Wrap|Line Numbers
  1. Dim CDOMailObj
  2. Set CDOMailObj = CreateObject("CDO.Message") 
  3. CDOMailObj.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
  4. CDOMailObj.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "the ip address of your smtpserver"
  5. CDOMailObj.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
  6. CDOMailObj.Configuration.Fields.Update  
  7.  
  8. CDOMailObj.From = "God@Heaven.com"
  9. CDOMailObj.To= "You@YourPlace.com" 
  10. CDOMailObj.cc= "everyone@earth.com"
  11. CDOMailObj.BCC= "Satan@hell.com"
  12. CDOMailObj.Subject = "I am watching you" 
  13.   CDOMailObj.TextBody = "Be very very carefull"
  14. 'for an ordinary text email
  15.  
  16. 'or    
  17.  
  18. 'CDOMailObj.HTMLBody = "Be <style 'color:red;'>very</style> very careful"
  19. 'for an html email
  20.  
  21. CDOMailObj.send
  22. set CDOMailObj=nothing
  23.  
paste that into a text file and change the .txt extension to .vbs and run it.
Jul 23 '10 #13

Delerna
Expert 100+
P: 1,134
PS

for CDO to work you do need to go into
"Add/Remove window components"

and ensure that the "SMTP server" under IIS (internet Information Services) is installed on the computer that will be sending emails via CDO. For that you will need a PRO version of the windows operating system you are running.

I get around that by using asp web applications. That way the emails are sent from our web server instead of the client pc and only the web server needs the smtp service installed...which it probably already does. Its also more secure that way

My asp pages connect to my database to retrieve any data I want to send.
All the client needs is enough data to provide them with a way for them to tell my asp app what they want to do and the asp app does the rest
Jul 23 '10 #14

ck9663
Expert 2.5K+
P: 2,878
CK here from (mostly in) SQL Server forum. I know this post is old, but my problem is kind'a similar...Do you have an VBA online article or sample code that I can use to connect my Outlook 2007 to SQL Server via VBA?

I received a formatted email that a certain process is completed. In subject, I can parse it to get the parameters I need as input to start a SQL Server SP. I've been trying google to find some sample codes but the sample code I got is showing an error.

Expand|Select|Wrap|Line Numbers
  1. Public Sub Application_NewMailex(ByVal EntryIDCollection As String)
  2.    Dim dbConn As Connection
  3.    Set dbConn = New ADODB.Connection
  4.  
  5.    dbConn.ConnectionString = "DRIVER={SQL Server};SERVER=(local);DATABASE=MyDB;UID=;PWD=;"
  6.  
  7.    dbConn.Open
  8.    'On Error Resume Next
  9.  
  10.  
  11.   If dbConn.State = adStateOpen Then
  12.       MsgBox "Welcome to DB"
  13.    Else
  14.       MsgBox "Sorry. No DB today."
  15.    End If
  16.  
  17.    ' Close the connection.
  18.    dbConn.Close
  19.  
  20.  
  21. End Sub
  22.  
  23.  
  24.  
The Dim dbConn As Connection is giving me an error.

Thanks...
Jan 12 '11 #15

100+
P: 207
More than likely you need to do two things to get your code to work.

First fully qualify your connection by doing

Dim dbConn As ADODB.connection

Secondly you will probably need to set a reference to Microsoft ActiveX Data Objects. To do this in Outlook go to the Visual Basic Editor. Then go Tools -> References. Then scroll down until you find Microsoft ActiveX Data Objects 2.8 Library. The number might be different 2.8 I believe is the newest one for Office 2007.

Your code should compile after doing this.

Also don't forget to release the memory for your dbConn
Jan 12 '11 #16

ck9663
Expert 2.5K+
P: 2,878
Hype, thanks a lot. Worked perfectly.

~~ CK
Jan 12 '11 #17

Post your reply

Sign in to post your reply or Sign up for a free account.