Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Automatically query and email results (Read 5065 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Automatically query and email results
23.01.08 at 22:14:22
 
Automatically query and email results
 
Is it possible to create a spreadsheet that has the database query in it – and send it out to all 13 of my stores for them to use (assuming the database info is the same) and them be able to use it without having the SQL*XL software installed? OR would I need a license for all locations and if so, do you have a “run time” license?
 
Answer:
It is possible to record macros with SQL*XL. You can record a macro to connect, query and disconnect. This will give you the data.  
You can further create a macro to send the sheet by email. See the file menu in Excel. There is an option to send the sheet to a recipient by email. Simpel editing of the macro code will allow you to change the list of recipients.  
 
You can manually run these macros or automate it so they run when the workbook opens. Just ask if I need to tell you how to run a set of macros when a workbook is opened.  
 
One license for the scenario would suffice as the recipients of the email just get the resulting worksheet.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
glennes
YaBB Newbies
*


I Love SQL*XL

Posts: 21
Re: Automatically query and email results
Reply #1 - 17.05.08 at 19:08:42
 
We use Lotus Notes for email. Is there a way to automate sending the results via Notes email instead of via Outlook, which is the way Excel likes to do it when selecting the Send To option?
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Automatically query and email results
Reply #2 - 18.05.08 at 10:40:41
 
I have no experience with Lotus Notes but after looking it up it seems certainly possible. Please see the following article that describes how to code it in Visual Basic. You can just use this code in Excel VBA too:
 
http://vbadud.blogspot.com/2007/10/automate-lotus-notes-email-using-visual.html
 
To summarise the code, these are the essential lines:
 
Set Session = CreateObject("Lotus.NotesSession")
Call Session.Initialize  
 
Set Maildb = Session.GETDATABASE("", "c:\notes\data\mail\mymail.nsf")
If Not Maildb.IsOpen = True Then
  Call Maildb.Open
End If
 
'Create the mail document
Set MailDoc = Maildb.CREATEDOCUMENT
Call MailDoc.ReplaceItemValue("Form", "Memo")
Call MailDoc.ReplaceItemValue("SendTo", "John Doe")
Call MailDoc.ReplaceItemValue("Subject", "Subject Text")
 
Set Body = MailDoc.CREATERICHTEXTITEM("Body")
Call Body.APPENDTEXT("Body text here")
 
Call Body.ADDNEWLINE(2)
Call Body.EMBEDOBJECT(1454, "", "C:\filename", "Attachment")
 
'Send the document
Call MailDoc.ReplaceItemValue("PostedDate", Now())
Call MailDoc.SEND(False)
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1