How To Export Access Table As Outlook Contact Group Without First Using Excel
Aug 6, 2012
I have an Access 2007 SQL database with 20 users. The DB has a list of about 3,000 email addresses that I want to export to Outlook for mass mailing. I've already got the services of an SMTP mail service that allows 1000 mails at a pop to be sent, and I've already written in the logic to separate the list into groups of 1k.
The problem I'm having is that I'm currently exporting from Access 2007 to Excel 2003 format (for some unknown reason Outlook 2007 does not directly interface with Access 2007), and then I have to manually go through the import steps within Outlook to bring the data from each 1k name group spreadsheet into a contact group for mailing.
It all works fine as it is, but it's clunky as hell. I want to automate that process, but I'm not very familiar with Outlook. I'm not even sure what the file format is for a group. Surely it must have its own format, like group1.xxx, or something. My ultimate objective is to export straight from access to the .xxx (whatever it is) format, direct it straight to the appropriate folder within Outlook and in the process eliminate the need for the more tedious steps required to import each Excel file within Outlook that I'm currently using.
View Replies
ADVERTISEMENT
Feb 3, 2015
I'm using an unbound text box and a command button to filter a list of contacts on a continuous form. The continuous form has a query for the record source and the list ends up showing only what is in the strWhere filter
Me.Filter = strWhere
Me.FilterOn = True
Is there a way to have Access 2003 take this filtered list and populate the To: field in Outlook or whatever the default mail client is? Each email needs to be deliminated by ";"
View 5 Replies
View Related
Jan 8, 2014
Microsoft has a Desktop Student Database template (Access 2010) that does exactly what I'm trying to do:
open the Outlook Contacts dialog box, select a contact from the GAL, and then programmatically save it to a record in one of my tables.
I've tried simulating the button/coding on my data entry form, but I'm getting the error:
"The command or action 'AddFromOutlook' isn't available now"
I've search high and low but have only found a reference to creating a temp contacts table to import all the contacts.
View 2 Replies
View Related
May 31, 2012
How to I can to add contact with field "Notes" (Type: Memo) in MS Access to field "Notes" in Outlook contact
Is it below ???
item.Notes = varNotes & ""
View 8 Replies
View Related
Jul 30, 2006
Hi,
Does anyone know how to export a pivot table to Ms Excel without using the specific button in the Pivot Table View of the form?
Thanks for the help:confused:
View 5 Replies
View Related
Mar 3, 2008
I have an export function below that will export my table "Test" to an Excel Spreadsheet.
However I want it so i can choose where that data in the "Test" table will go in the Excel Spreadsheet i.e. I want to export all the data in to Cell "B2" of the SpreadSheet - at the moment it will export all the data into "A1"
Any help or ideas?
Private Sub Command3_Click()
'Export function
'EXPORTS TABLE IN ACCESS DATABASE TO EXCEL
'REFERENCE TO DAO IS REQUIRED
Dim strExcelFile As String
Dim strWorksheet As String
Dim strDB As String
Dim strTable As String
Dim objDB As Database
'Change Based on your needs, or use
'as parameters to the sub
strExcelFile = "E:CSCLDMSLDMSDatabaseAppLDMS_Spec.xls"
strWorksheet = "WorkSheet1"
strDB = "E:CSCLDMSLDMSDatabaseAppLDMS_IFF_APP.mdb"
strTable = "Test"
Set objDB = OpenDatabase(strDB)
'If excel file already exists, you can delete it here
If Dir(strExcelFile) <> "" Then Kill strExcelFile
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
objDB.Close
Set objDB = Nothing
End Sub
View 2 Replies
View Related
Sep 15, 2014
I have a table (tbloutput) which has details of customers and which staff they have been contacted by.
What i want to do is, export the details from this table into an excel sheet using a template that i have set.
What i want to do is create multiple excel outputs using this template depending on the name of the staff. So each staff will have a seperate workbook which was created using that template. And i also want the new workbook to be named for that staff member.
So in short
Table exported to excel workbook and excel workbook named : Blabla staffname.xlsm
View 1 Replies
View Related
Apr 16, 2015
i have the following code and it runs without error but when i want to open excel file, i have the following message and i can't open it.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tbl_userinformation", "G:Rasteh MonaName.xlsx", True
View 3 Replies
View Related
Jun 21, 2012
How to export a MS Access table or Query to and Excel file and it works great. How to make this code Export multiple table into one Excel file/
here is the Function:
HTML Code:
Sub ExportData_Sheet_Basic()
On Error GoTo ExportData_Error
'DAO objects to get the data
Dim db As DAO.Database
Dim rs As DAO.Recordset
[code]....
View 5 Replies
View Related
Aug 19, 2011
I am new to the 2010 version and I am working on downloading contact sheets from excel into access. The download is complete and I see all the sheets and can filter, but my contacts page is still empty. How do I get the contacts from the different sheets into my contact list?
View 1 Replies
View Related
Sep 1, 2013
For instance, first table export to EXCEL CELL A1 and then second table export to the same EXCEL but to CELL A5! I simply do not know the sytax to tell ACCESS to do the correct export!
e.g. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "EXPORTDATA", "c:EXCELSHEET.xls", True
View 3 Replies
View Related
Oct 18, 2005
Hi all,
I have an access Db that I use to manage contacts etc. I regularily send out newsletters and emails to the contacts in that Db.
I want to know if there is a way I can essentially track the emails from access in outlook.
eg. based on field "email" it can see the emails incoming and outgoing in Outlook and makes a new record in my table "communications" that also tracks phone calls and other comm's.
Tbls - Contacts, Communications, Company
It would be great if all email communications could just automatically be listed in the access table communications with a link to the email in outlook (not the actual email, only the subject line and a hyperlink to the email)
I have been scouring the web for something to do this and have found lots of kinda's or not quite's. I don't want to import my outlook email to a table if possible.
I run Outlook and Access 2003 (Office 2003) on XP SP2 box.
Thanks Heaps if you have any idea what I am after!
C
View 9 Replies
View Related
Aug 29, 2014
I have a form that uses the command AddContactfromOutlook. I used the standard example given by Microsoft and it works flawlessly in the form. When I use the form as a subform this command button breaks.
View 3 Replies
View Related
Feb 16, 2006
I have the following code working great with creating new contacts in Outlook from Access. My problem is I can't figure out a way to update already existing contacts in Outlook.
Function AddContacts()
Dim OutlookObj As Outlook.Application
Dim Nms As Outlook.NameSpace
Dim MyContacts As Object
Dim MyItems As Object
Dim MyItem As Object
Dim Db As Database
Dim Rst As Recordset
Set Db = CurrentDb
Set Rst = Db.OpenRecordset("CustomerOutlook", dbOpenDynaset)
Set OutlookObj = CreateObject("Outlook.application")
Set Nms = OutlookObj.GetNamespace("MAPI")
'to point to a nested folder in Outlook you have to create folder items in folder items:
Set MyContacts = Nms.Folders.Item("Public Folders").Folders.Item("All Public Folders").Folders.Item("Our Contacts")
Set MyItems = MyContacts.Items
While Not Rst.EOF
'Declare which form to be used to add your contacts
Set MyItem = MyItems.Add("IPM.Contact")
'add fields, to find out which fields are available take a look at all the available members of the
'ContactItem class in the Outlook object library.
'Be careful, Microsoft is using various names for outlook fields!
MyItem.CompanyName = Rst!BusinessName
MyItem.FirstName = Rst!CFirst
MyItem.LastName = Rst!CLast
If Not IsNothing(Me.WebSite) Then
MyItem.WebPage = Rst!WebSite
End If
If Not IsNothing(Me.CMiddle) Then
MyItem.MiddleName = Rst!CMiddle
End If
If Not IsNothing(Me.Suffix) Then
MyItem.Suffix = Rst!Suffix
End If
MyItem.JobTitle = Rst!JobTitle
MyItem.BusinessTelephoneNumber = Rst!Phone
MyItem.Business2TelephoneNumber = Rst!BackPhone
MyItem.MobileTelephoneNumber = Rst!Mobile
MyItem.BusinessFaxNumber = Rst!Fax
MyItem.Email1Address = Rst!Email
If Not IsNothing(Me.Address1) Then
If Not IsNothing(Me.Address2) Then
MyItem.BusinessAddressStreet = Rst!Address1 & ", " & Rst!Address2
Else
MyItem.BusinessAddressStreet = Rst!Address1
End If
End If
MyItem.BusinessAddressCity = Rst!CCity
MyItem.BusinessAddressState = Rst!CState
MyItem.BusinessAddressPostalCode = Rst!PostalCode
MyItem.Categories = Rst!CustomerType
MyItem.FileAs = Rst!BusinessName
'MyItem.Etc = Rst!Etc
MyItem.Close (olSave)
Rst.MoveNext
Wend
End Function
View 2 Replies
View Related
Jul 26, 2015
I am trying to use
RunMenuCommand
command : AddContactFromOutlook
But i get this error message
"The command or action AddFromOutlook isn't available now "
on a demo access "project manager" it works but in my data base does not ...
View 1 Replies
View Related
Nov 3, 2004
I've researched all the email topics, but can't find what I am trying to do.
Most of these topics are talking about emailing a record or form or something.
I have a form that contains thousands of contacts address, telephone, email, etc.
I would like to create a button that on click would open Outlook and grab ONLY the
email of the current contact I am looking at on the form at that time and add that
email address to the [To:] box of a new email.
Thanks you guys, I'm learning...slowly, but it's coming.
View 4 Replies
View Related
Apr 25, 2013
I am using Excel and Access 2010.
I have an excel spreadsheet with 8 tabs. They are all in the same format and column order. They are employees grouped by region. My ultimate goal is to merge all of these onto one excel tab, relatively instantly. I created a master tab and tried doing array formulas and Vlookups, it worked but my spreadsheet was way too slow.
My solution? Import and link them to an Access database, step complete. Create an XML export then import into Excel.
My problem? The only way to update the excel tab with the combined tabs is to save the excel file after changes, go back into Access, re-export to XML, then go back into excel and refresh the data.
My questions, is there any way to automate this process to the point that I can change excel, save, then hit refresh on my excel tab with the XML import to auto-update?
View 7 Replies
View Related
Dec 13, 2012
I am using Access 2010 and Excel 2010. I need to have VB script to export the access table 502 records by 38 fields into Multiple Excel workbooks each having multiple tabs. In the Access table each record has two fields: Div and Tab that will be used to name each workbook and each tab (sheet). There are 6 unique "Div"'s to name the 6 workbooks and there are several "Tab" names for each Div (workbook).
Note: These 6 workbooks with multiple tabs were originally imported into Access from one common folder on my desktop by this routine:
Option Compare Database
Option Explicit
Private Sub Command1_Click()
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long
[Code] .....
View 7 Replies
View Related
Dec 13, 2012
I am using Access 2010 and Excel 2010. I need to have VB script to export the access table 502 records by 38 fields into Multiple Excel workbooks each having multiple tabs. In the Access table each record has two fields: Div and Tab that will be used to name each workbook and each tab (sheet). There are 6 unique "Div"'s to name the 6 workbooks and there are several "Tab" names for each Div (workbook).
Excel workbooks would take names from the "Div" field and the tab names would come from the "Tab" field in the Access table. First need to find workbook name (Div - Field) then the look for each sheet name (Tab - Field) to create 1st Excel workbook with all the sheets (Tab) and repeat the process. I think you need to approach of read the Access table one record at a time keying on the "Div" and "Tab" fields in creating each Excel workbook with the associated multiple tabs (sheets) that are written to a common folder.
Note: These 6 workbooks with multiple tabs were originally imported into Access from one common folder on my desktop by this routine.
Option Compare Database
Option Explicit
Private Sub Command1_Click()
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
[code]....
View 12 Replies
View Related
Nov 23, 2007
What can be the reason ?
i'm doing it through a regular "Import Spreadsheet Wizard", and get a message "an error occured trying to import file"
File is not open. Made a copy under a different name; still can't import..
View 4 Replies
View Related
May 18, 2005
I would like to export a access report to excel. All rows and columns exported correctly except from text fields (missing or change to a strange number). I've also think about to write a vb script to export the result recordset of the report to the excel. But the report is a report of Sales which containing a grand total amount of the group of records. Is there a way to write a script to achieve this (the grand total amount is not include in the recordset)?
Thanks!
View 2 Replies
View Related
Dec 16, 2005
Good morning all,
First, I know there have been a lot of posts on this topic already, and I've printed out several of them to try to help me solve my problem. However, I'm having a problem getting mine to work.
Here's what I want to do, I am trying to export four different queries into one workbook, separate worksheets in Excel. For example, I have qryControl, qryLocal, qryPar and qryNasco. I want to export qryControl into the CONTROL worksheet in General Ledger.xls, qryLocal into the LOCAL worksheet in General Ledger.xls, and so on and so forth. Can this be done?
I started a practice form and added a command button based on some of the information I read before, just to see if I can get a query to transfer period and I ran into some problems. Here is the code I used:
Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryWorldWideClaims", "C: emp.xls", True
'Create Excel Instance
Dim xlObj As Object
Set xlObj = CreateObject("Excel.Application")
'Open the template file
xlObj.workbooks.Open "C:mytemplatefile.xls"
'Save the template file with another name
xlObj.activeworkbook.savesas "C:mynewfile.xls"
'Open the temp file we exported from Access
xlObj.workbooks.Open "C: emp.xls"
'Select and copy all the data
xlObj.activeworkbook.cells.select
xlObj.activeworkbook.selection.copy
'Re-activate the destination file
xlObj.workbooks("mynewfile.xls").Activate
'Select cell A1 on the first sheet
xlObj.activeworkbook.sheets(1).range("A1").select
'Paste the data
xlObj.activesheet.paste
'Save and close the file
xlObj.activeworkbook.Save
xlObj.activeworkbook.Close
'Close the other file - assumes no other Excel files are open
xlObj.activeworkbook.Save
xlObj.actoveworkbook.Close
xlObj.Quit
Set xlObj = Nothing
End Sub
I get an error message at the following line:
xlobj.activeworkbook.saveas "C:mynewfile.xls"
The error is:
'Runtime Error 438' Object doesn't support this property or method.
Can somebody please tell me if I can export to four different worksheets in the same workbook and also what I need to do to resolve my error.
Thanks every body!
View 2 Replies
View Related
Dec 31, 2005
Folks,
I have a form with combo box (cboProjForRptSeltn ) and two command buttons. The combo box is filled with name of the reports. The combo box has two columns, column 1 is the name of the report and column 2 is the bound column which also is the link field (primary key) to limit the records.
When the user selects an item from cbo box and click the "Preview Risk Table" command button it will open a report in preview mode. The report's "Record Source" property is set to a UNION query (qryRprtRskTbl) which pull the records from several tables. The report's "Filter" property is set to the following criteria:
“qryRprtRskTbl.P.intProjectId=Forms!frmReportSelec tionBlrR1!cboProjForRptSeltn”
The above criteria is nothing but filter based on the value of the cbo box.
The click event procedure is as follows:
Private Sub cmdPreviewRprt_Click()
Dim strDocName As String
strDocName = "rptRskTblProjectWise"
DoCmd.OpenReport strDocName, acPreview, "qryRprtRskTblFilter_r1"
End Sub
"qryRprtRskTblFilter_r1" is another query out of the UNION query I mentioned above (qryRprtRskTbl). qryRprtRskTblFilter_r1 is pulling all the records from qryRprtRskTbl which meets the projectID field selected in cbo box, which is also the same as the "Filter" property value of the report as indicated above.
Everything works fine with cbo box and "Preview Risk Table" command button. It just pull all the records for the project selected under cbo box and display it as report in preview mode. Looks great!!
Here's my problem. I wanted to export the same report that was previewed by the user to Excel. For this I am using, another command button called “Export to Excel”. The click event of this procedure is as shown below:
Private Sub cmdExportToExcel_Click()
On Error Resume Next
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook
Dim acQuery As QueryDef
Dim objRST As Recordset
Dim strQueryName As String
Dim strSearch As String
'Dim strSQL As String
'strSearch = Me![cboProjForRptSeltn]
strQueryName = "qryRprtRskTblFilter_r1"
'strQueryName = "qryEffcyAllProjtsForRprt"
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Add
Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)
'Loop through the fileds collection and make each field name a column heading in Excel
Set xlSheet = xlWorkbook.Sheets(1)
For lvlColumn = 0 To objRST.Fields.Count - 1
xlSheet.Cells(1, lvlColumn + 1).Value = objRST.Fields(lvlColumn).Name
Next
'Change the font to bold for header row
xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, objRST.Fields.Count)).Font.Bold = True
' I have some codes here for formatting Excel cells …
'Send data from Recordset out to Excel
With xlSheet
.Range("A2").CopyFromRecordset objRST
.Name = Left(strQueryName, 31)
End With
Set xlSheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing
End Sub
When I click the command button, it loads an instance of Excel and adds a workbook under the query name "qryRprtRskTblFilter_r1", but no data.
If I use another query say, "qryEffcyAllProjtsForRprt", which has no connection to cbo box value then it is cool, exports all the data to Excel without any problem.
I guess, you folks understand what I am trying to achieve here. Basically, I wanted to give the user some flexibility, either they can view the data as Access report or Export to Excel with same formatting feature and add more later if they want after exporting, as they see on Access report preview.
I have attached some of the query files I have described here. May be I can clarify more down the road, if necessary.
My bottom line question is: why the “qryRprtRskTblFilter_r1” query runs perfectly on Access reports but not when I want to run to export to Excel?
I don’t want to use the TransferSpreadsheet or outputTo method of docmd object, because I wanted to do some formatting before I export to excel.
Any help is greatly appreciated.
Thanks
ShanVel
View 7 Replies
View Related
Jul 19, 2005
Dear Access Experts:
I am using Access 2000. I have created a command button on a form that when pressed, it exports a file to Excel using the TransferSpreadsheet command. It exports names, degree type, graduation date starting in cell A1. It works well.
What I wish to do is this: Can I have this data exported starting in cell A5 instead of A1? AND in the export cell A1 will have "College", A2 will have "School" and A3 will have "Dean".
Any help is appreciated.
Regards,
Dion
View 1 Replies
View Related
Oct 26, 2007
Hello all...I'm having a problem when exporting some text from Access 2000/XP to Excel using the EXPORT function. The reason why report is used to export is b'cos I need certain layout & to be ported over to Excel.
Text like '00133484-001' or '00130898-001' will be changed to another number after EXPORT, can anyone help me in this ? How can I set these numbers so that they remain the same ?
View 3 Replies
View Related
Apr 24, 2014
I have 260 access files (12 data tables in each file). I want to extract table with name of "Borrower" and "Ledger Recovery" from DB to excel. The end result i need is to consolidate all 260 tables of "Borrower" in one excel sheet and all 260 tables of "Ledger Recovery" in one excel sheet.
View 1 Replies
View Related
