|
Code Samples: Excel and Access, LLC
This area of our
website is for examples of Expert Excel and Access VBA (Visual Basic
for Applications). It is written by Expert Excel Consultants
and Expert Access Programmers.
Excel
and Access, LLC
Info@ExcelandAccessLLC.Com
714.262.6893
------------------------------
This
examples shows you how to ...
CallOption Compare
Database
Option Explicit
Private Sub
Command1_Click()
Dim DB As
Database
Dim rst As
Recordset
Dim sql As
String
Set DB =
CurrentDb
sql = "SELECT
Email FROM Email;"
Set rst =
DB.OpenRecordset(sql)
rst.MoveFirst
Do Until
rst.EOF
Dim AppOutLook As
Outlook.Application
Dim MailOutLook As
Outlook.MailItem
Set AppOutLook =
CreateObject("Outlook.Application")
Set MailOutLook =
AppOutLook.CreateItemFromTemplate(CurrentProject.Path &
"\OutlookTemplateFile.oft")
Dim xnamemail As
String
xnamemail =
"Default"
Call
AppOutLook.GetNamespace("MAPI").Logon(Profile:=xnamemail)
With
MailOutLook
.BodyFormat =
olFormatHTML
.TO =
rst.Fields(0)
.Subject = "Place Subject
Here"
.Importance =
olImportanceHigh
.Send
End
With
rst.MoveNext
Loop
End Sub
or E-mail
to
------------------------------
This
examples shows you how to
.
------------------------------
Here are some useful ones
I often post to answers on EE -- the first gets a file name
(generally saved to a variable for use in code), and the second gets
a folder name. They require setting a reference to the
appropriate version of the Microsoft Office object
library.
One:
Public Function
SelectFile() As String
'Requires Office XP (2002) or
higher
'Requires a reference to the Microsoft Office Object
Library
'Created by Helen Feddema 3-Aug-2009
'Last modified
3-Aug-2009
On Error GoTo ErrorHandler
Dim fd As Office.FileDialog
Dim
varSelectedItem As Variant
Dim strFileNameAndPath As
String
'Create a FileDialog object
as a File Picker dialog box.
Set fd =
Application.FileDialog(msoFileDialogFilePicker)
With fd
'Set
AllowMultiSelect to True to allow selection of multiple
files
.AllowMultiSelect =
False
.Title = "Browse for
File"
.ButtonName =
"Select"
.Filters.Clear
.Filters.Add
"Documents", "*.doc; *.txt", 1
.InitialView =
msoFileDialogViewDetails
If .Show
= -1 Then
'Get
selected item in the FileDialogSelectedItems
collection
For
Each varSelectedItem In
.SelectedItems
strFileNameAndPath =
CStr(varSelectedItem)
Next varSelectedItem
Else
Debug.Print
"User pressed
Cancel"
strFileNameAndPath = ""
End
If
End With
SelectFile = strFileNameAndPath
ErrorHandlerExit:
Set fd =
Nothing
Exit Function
ErrorHandler:
MsgBox "Error No: " &
Err.Number & "; Description: " &
Err.Description
Resume ErrorHandlerExit
End Function
------------------------------
Two:
Public
Function SelectFolder() As String
'Requires Office XP (2002) or
higher
'Requires a reference to the Microsoft Office Object
Library
'Created by Helen Feddema 3-Aug-2009
'Last modified
3-Aug-2009
On Error GoTo ErrorHandler
Dim strFolderPath As String
Dim fd
As Office.FileDialog
Dim strPath As String
'Create a FileDialog object as a Folder Picker
dialog box.
Set fd =
Application.FileDialog(msoFileDialogFolderPicker)
'Set strPath to the folder you want to open
initially
strPath = "G:\Data"
With fd
.Title =
"Browse for folder where _________ are
located"
.ButtonName =
"Select"
.InitialView =
msoFileDialogViewDetails
'.InitialFileName = strPath
If
.Show = -1 Then
strFolderPath = CStr(fd.SelectedItems.Item(1)) &
"\"
Else
Debug.Print
"User pressed
Cancel"
strFolderPath = ""
End
If
End With
SelectFolder = strFolderPath
ErrorHandlerExit:
Exit Function
ErrorHandler:
MsgBox "Error No: " &
Err.Number & "; Description: " &
Err.Description
Resume ErrorHandlerExit
End Function
Helen
Feddema
------------------------------
This
examples shows you how to ...
[ Home ]
| 
