Code/macro to email all tables in DB?

[dileshw]

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!

[Delerna]

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

[dileshw]

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...

[Delerna]

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.

[dileshw]

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)

[Delerna]

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

[Delerna]

PS the same code works for me so.....?

[dileshw]

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.

[Delerna]

Sorry My code wasn't identical, I had the attachment part remmed out and it is not correct.

Try this
[code=sql]
Private Sub email1_Click()
Dim OlApp As Outlook.Applica tion, oMail As MailItem
Set OlApp = CreateObject("O utlook.Applicat ion")
Set oMail = OlApp.CreateIte m(olMailItem)
oMail.To = "dileshXXX@hotm ail.com"
oMail.To = "graham.taylor@ btequipment.com .au"
oMail.Subject = "test Auto email"

Mail.Attachment s.Add "D:\\Database\t bl_PO.xlso"
'Mail.Attachmen ts.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
[/code]

[dileshw]

hey delerna,
its still very much the same. Its just that the button doesnt start outlook (or send the mail to the "outbox")
:(

[Delerna]

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.

[dileshw]

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?

[Delerna]

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
[code=vba]
Dim CDOMailObj
Set CDOMailObj = CreateObject("C DO.Message")
CDOMailObj.Conf iguration.Field s.Item("http://schemas.microso ft.com/cdo/configuration/sendusing") = 2
CDOMailObj.Conf iguration.Field s.Item("http://schemas.microso ft.com/cdo/configuration/smtpserver") = "the ip address of your smtpserver"
CDOMailObj.Conf iguration.Field s.Item("http://schemas.microso ft.com/cdo/configuration/smtpserverport" ) = 25
CDOMailObj.Conf iguration.Field s.Update

CDOMailObj.From = "God@Heaven.com "
CDOMailObj.To= "You@YourPlace. com"
CDOMailObj.cc= "everyone@earth .com"
CDOMailObj.BCC= "Satan@hell.com "
CDOMailObj.Subj ect = "I am watching you"
CDOMailObj.Text Body = "Be very very carefull"
'for an ordinary text email

'or

'CDOMailObj.HTM LBody = "Be <style 'color:red;'>ve ry</style> very careful"
'for an html email

CDOMailObj.send
set CDOMailObj=noth ing
[/code]

paste that into a text file and change the .txt extension to .vbs and run it.

[Delerna]

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...whi ch 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

[ck9663]

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...