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!
16 4379
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. -
1) You could link an excel spreadsheet into access
-
2) When the button is clicked
-
a) run a delete query that deletes all
-
records from the linked spreadsheet
-
b) run an insert query that selects all
-
records from a table and inserts them
-
into the linked excel table
-
Now you can email the excel document containing the inserted table contents in anyway you wish
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...
Ok, perhaps this code will help -
Private Sub cmdSendMail_Click()
-
Dim OlApp As Outlook.Application, oMail As MailItem
-
Set OlApp = CreateObject("Outlook.Application")
-
Set oMail = OlApp.CreateItem(olMailItem)
-
oMail.To = "TheEmailAddress"
-
oMail.Subject = "TheSubject"
-
oMail.Attachments = "TheFileToAttach.xls"
-
oMail.Body = "Here it is"
-
oMail.Send
-
Set oMail = Nothing
-
Set OlApp = Nothing
-
End Sub
-
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.
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)
Multiple attachments -
oMail.Attachments = "TheFileToAttach.xls;" & "TheOtherFileToAttach.xls;" & "AThirdFileToAttach.xls;"
-
Multiple recipients -
oMail.To = "TheEmailAddress;AnotherEmailAddress;YetAnother"
-
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
PS the same code works for me so.....?
it hasnt eached the recipient> not even the outbox of the sender(me).
The exact code i tried out. -
Private Sub email1_Click()
-
Dim OlApp As Outlook.Application, oMail As MailItem
-
Set OlApp = CreateObject("Outlook.Application")
-
Set oMail = OlApp.CreateItem(olMailItem)
-
oMail.to = "dileshXXX@hotmail.com"
-
oMail.Subject = "test Auto email"
-
oMail.Attachments = "D:\Database\tbl_PO.xls"
-
oMail.Body = "Sending table via autpmail. Here it is"
-
oMail.Send
-
Set oMail = Nothing
-
Set OlApp = Nothing
-
End Sub
-
I have ticked the outlook ref library.
I have also have Outlook open and connected to my exchange server.
Sorry My code wasn't identical, I had the attachment part remmed out and it is not correct.
Try this -
Private Sub email1_Click()
-
Dim OlApp As Outlook.Application, oMail As MailItem
-
Set OlApp = CreateObject("Outlook.Application")
-
Set oMail = OlApp.CreateItem(olMailItem)
-
oMail.To = "dileshXXX@hotmail.com"
-
oMail.To = "graham.taylor@btequipment.com.au"
-
oMail.Subject = "test Auto email"
-
-
Mail.Attachments.Add "D:\\Database\tbl_PO.xlso"
-
'Mail.Attachments.Add "One Of These For Each Attachment
-
-
oMail.Body = "Sending table via autpmail. Here it is"
-
oMail.Send
-
Set oMail = Nothing
-
Set OlApp = Nothing
-
End Sub
-
hey delerna,
its still very much the same. Its just that the button doesnt start outlook (or send the mail to the "outbox")
:(
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.
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?
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 -
Dim CDOMailObj
-
Set CDOMailObj = CreateObject("CDO.Message")
-
CDOMailObj.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
-
CDOMailObj.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "the ip address of your smtpserver"
-
CDOMailObj.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
-
CDOMailObj.Configuration.Fields.Update
-
-
CDOMailObj.From = "God@Heaven.com"
-
CDOMailObj.To= "You@YourPlace.com"
-
CDOMailObj.cc= "everyone@earth.com"
-
CDOMailObj.BCC= "Satan@hell.com"
-
CDOMailObj.Subject = "I am watching you"
-
CDOMailObj.TextBody = "Be very very carefull"
-
'for an ordinary text email
-
-
'or
-
-
'CDOMailObj.HTMLBody = "Be <style 'color:red;'>very</style> very careful"
-
'for an html email
-
-
CDOMailObj.send
-
set CDOMailObj=nothing
-
paste that into a text file and change the .txt extension to .vbs and run it.
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
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. -
Public Sub Application_NewMailex(ByVal EntryIDCollection As String)
-
Dim dbConn As Connection
-
Set dbConn = New ADODB.Connection
-
-
dbConn.ConnectionString = "DRIVER={SQL Server};SERVER=(local);DATABASE=MyDB;UID=;PWD=;"
-
-
dbConn.Open
-
'On Error Resume Next
-
-
-
If dbConn.State = adStateOpen Then
-
MsgBox "Welcome to DB"
-
Else
-
MsgBox "Sorry. No DB today."
-
End If
-
-
' Close the connection.
-
dbConn.Close
-
-
-
End Sub
-
-
-
The Dim dbConn As Connection is giving me an error.
Thanks...
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
Hype, thanks a lot. Worked perfectly.
~~ CK
Sign in to post your reply or Sign up for a free account.
Similar topics
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
*/
|
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...
|
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...
|
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...
|
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...
|
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.
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |