Access VBA Macros

Start here

Macro to know the names of reports in a database

If you want to know the names of reports saved or created in a database . Try  this – 

Sub report_names()
Dim rpt As Object
For Each rpt In Application.CurrentProject.AllReports
MsgBox rpt.Name
Next
End Sub

Open Access form using vba

Sub open_an_form()
Dim frmname As String
frmname = “F_1”
DoCmd.OpenForm frmname, acNormal
End Sub

To know more visit
http://msdn.microsoft.com/en-us/library/office/aa156353(v=office.10).aspx

Macro to know the names of forms in a database

Sub names_of_all_forms()

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject

For Each obj In dbs.AllForms
MsgBox (obj.Name)
Next obj

Set dbs = Nothing

End Sub

Macro to know the names of queries in a database

Sub names_of_queries()
Dim qry As DAO.QueryDef
For Each qry In CurrentDb.QueryDefs
If Left(qry.Name, 1) <> “~” Then
MsgBox qry.Name
End If
Next
End Sub

Know field names/headers of any table

Macro to know the field names and headers of any table –

Sub get_all_field_names()

Dim i As Integer
Dim tblname As String
tblname = “sample”
For i = 0 To CurrentDb.TableDefs(tblname).Fields.Count – 1
MsgBox CurrentDb.TableDefs(tblname).Fields(i).Name
Next

End Sub

 

Macro to know the names of tables in a database

`

Option Compare Database
Sub all_table_names()
For Each tbl In CurrentDb.TableDefs
If tbl.Attributes = 0 Then
MsgBox tbl.Name
End If
Next
End Sub

`

Sending Email from Access using Docmd.sendobject

Microsoft Access allows us to send any access object as attachment through outlook by using “Docmd.sendobject”

To know about Docmd.sendobject visit the below link
http://msdn.microsoft.com/en-us/library/bb214075(v=office.12).aspx

Syntax 
DoCmd.SendObject ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile

ObjectType – acSendTable( to send table), acSendquery(to send query),acsendreport(to send report),ascendform(to send form), acSendModule(to send module), acSendNoObject( do not send any object)

ObjectName – Name of table, query, Report etc.

OutputFormat – acFormatHTML ,acFormatRTF ,acFormatSNP,acFormatTXT ,acFormatXLS,acFormatXLSB, acFormatXLSX ,acFormatXPS,acFormatPDF

To – email address to be added in to field of outlook

Cc– email address to be added in cc field of outlook

Bcc– email address to be added in bcc field of outlook

Subject – subject of email

Message text – “content of email”

Edit message – If you want to display or make any changes in email before sending then set it True, else false

Templatefile – The full name, including the path, of the file to use as a template for an HTML file.

Download Sample Access Database

Example1 Send Table as Attachment

Sub send_table_using_send_object()
Dim mailto As String
Dim ccto As String
Dim bccto As String
mailto = “koul ashish”
ccto = “”
bccto = “”
emailmsg = “Hi Ashish,” & vbNewLine & vbNewLine & “Please find the report attached”
mailsub = “Sales Report Dec-2011”
On Error Resume Next
‘acFormatXLS will export the table into excel format and will add that excel file as attachment
DoCmd.SendObject acSendTable, “sales_detail”, acFormatXLS, mailto, ccto, bccto, mailsub, emailmsg, True
End Sub

Download Sample Access Database

Example2 Send Query as attachment

Sub send_query_using_send_object()
Dim mailto As String
Dim ccto As String
Dim bccto As String
mailto = “koul ashish”
ccto = “”
bccto = “”
emailmsg = “Hi Ashish,” & vbNewLine & vbNewLine & “Please find the report attached”
mailsub = “Sales Report Dec-2011”
On Error Resume Next
‘acFormatpdf will export the result of query into pdf format and will add the pdf as attachment
DoCmd.SendObject acSendQuery, “rep_name_a”, acFormatPDF, mailto, ccto, bccto, mailsub, emailmsg, True
End Sub

Download Sample Access Database

Example3 Send report as attachment

Sub send_report_using_send_object()
DoCmd.SetWarnings (False)
Dim mailto As String
Dim ccto As String
Dim bccto As String
mailto = “koul ashish”
ccto = “”
bccto = “”
emailmsg = “Hi Ashish,” & vbNewLine & vbNewLine & “Please find the report attached”
mailsub = “Sales Report Dec-2011”
On Error Resume Next
‘acFormatpdf will export the result of query into pdf format and will add the pdf as attachment
DoCmd.SendObject acSendReport, “sales_detail”, acFormatPDF, mailto, ccto, bccto, mailsub, emailmsg, True
DoCmd.SetWarnings (True)
End Sub

Download Sample Access Database

Example 4 Send a form as attachment

Sub send_form_using_send_object()
Dim mailto As String
Dim ccto As String
Dim bccto As String
mailto = “koul ashish”
ccto = “”
bccto = “”
emailmsg = “Hi Ashish,” & vbNewLine & vbNewLine & “Please find the report attached”
mailsub = “Sales Report Dec-2011”
On Error Resume Next
‘acFormatpdf will export the result of query into pdf format and will add the pdf as attachment
DoCmd.SendObject acSendForm, “sales_detail”, acFormatPDF, mailto, ccto, bccto, mailsub, emailmsg, True
End Sub

Download Sample Access Database