473,320 Members | 1,900 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Code/macro to email all tables in DB?

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
16 4379
Delerna
1,134 Expert 1GB
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
dileshw
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
1,134 Expert 1GB
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
dileshw
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
1,134 Expert 1GB
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
1,134 Expert 1GB
PS the same code works for me so.....?
Jul 21 '10 #7
dileshw
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
1,134 Expert 1GB
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
dileshw
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
1,134 Expert 1GB
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
dileshw
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
1,134 Expert 1GB
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
1,134 Expert 1GB
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
2,878 Expert 2GB
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
hype261
207 100+
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
2,878 Expert 2GB
Hype, thanks a lot. Worked perfectly.

~~ CK
Jan 12 '11 #17

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

Similar topics

1
by: Julia Sats | last post by:
Can I do something like it: if i = 0 then for cur_var in (select * from t0) loop else for cur_var in (select * from t1) loop /* a lot of lines here */
5
by: jdph40 | last post by:
I have a form in Access 97 on which I have an unbound list box filled with our employees names from tblEmployees. When you select an employee's name and click a button, the following code is run...
3
by: WindAndWaves | last post by:
Hi there, Can anyone tell me how I can run code/macro in a different database??? Below are the two situations where this may be applicable. 1. run a macro/code in another database that sends a...
1
by: robboll | last post by:
Using MS Access 2003 I am looking for a function that will search the entire Tables Collection for a specific string in text or memo fields. For example if I enter "widget" it interrogates the...
4
by: Jozef | last post by:
Hello, I'm trying to check for and add a field to a table to a back end database through code. The problem I've been faced with is changing permissions, because I have to use administer...
3
by: Sabri AKIN | last post by:
Hi, I want to send ascx file's html code as email.this ascx file is summary of customer sale.and at last iwant to send email to customer.how can i get and past ascx file's html code to mail body.
2
by: rlucas | last post by:
Okay, I'm forced to ask what is probably a stupid question. Using System.Web.Mail.Message and SMTPMail I can email messages--COOL! However, when I email an HTML file (loaded as a string using...
2
by: Trint Smith | last post by:
I need to allow someone to communicate by email from my webform without knowing the receivers email...Is there some vb.net code to do that using one of my servers email accounts as the sender of...
1
by: sbettadpur | last post by:
hi, how to create 3 tables in one html page,where 2 columns and 1 row will be there. 1st column table includes name,date,suggestions,emailid. 2nd column table includes previous suggestions or...
2
by: deve8ore | last post by:
Hello, I have built a macro to automatically go to a specific directory and a specific folder, however the names of the files I need to automatically locate change names every month. I.e. - I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.