Disable prompts when sending (Outlook) email via Access

If you have ever tried to automate the process of sending an email from within Access using VBA code, you may have seen this error message:

"A program is trying to automatically send e-mail on
your behalf. Do you want to Allow this?"

It makes you wait & then you HAVE to hit the Yes button for it to send the Email.

A SOLUTION that should work ...

Dim objOutlook As New Outlook.Application
Dim objMail As MailItem

Set objOutlook = New Outlook.Application
Set objMail = objOutlook.CreateItem(olMailItem)

EmailAddr = "xxx@AOL.com"
CopyEmailAddr = "yyy@AOL.com"
Subj = "Test subjject"
Body = "Body"
PathName = "C:\test.txt"

With objMail
.To = EmailAddr
.cc = CopyEmailAddr
.Subject = Subj
.Body = Body
.NoAging = True
.Attachments.Add PathName
.Display
End With

'****** Send the email without prompts *******
SendKeys "%{s}", True
'****************************************************

Set objMail = Nothing
Set objOutlook = Nothing

Use Microsoft Jet's ShowPlan to write more efficient queries

Use Microsoft Jet's ShowPlan to write more efficient queries

While reading some posts on the Access newsgroup, I came across this article about optimizing your Access queries. One of the most powerful features of SQL Server is being able to view the execution plan of a query. With this relatively simple registry hack, you can accomplish a very similar feature in Access, by forcing Access to export the execution plan to a text file.

Green bar reporting in Access

Many accounting style reports need help to be more readable. Ever used a ruler on a landscape report to make sure you read data from the correct row? Remember the old green bar reports from mainframes that made this easier? Well, you can accomplish the same type of layout on an Access report.

Make sure the BackStyle of each control is Transparent.

Code the Detail Format event:

If Me.Section(0).BackColor = vbWhite Then
Me.Section(0).BackColor = 12632256 ' gray
Else
Me.Section(0).BackColor = vbWhite
End If

Access your bookmarks online

MyBookmarks.com - Access your bookmarks online anytime, anywhere

I've always hated having to manage bookmarks at home, at work, on my laptop ... it gets very frustrating not having all my bookmarks on all my machines.

Now, with mybookmarks, I have a cool little icon in my system tray that I can use to quickly add any site to my bookmarks - on any machine. For example, if I'm at work and I find a cool site I want to bookmark, I add it to mybookmarks then when I get home or use my laptop, I can syncronize that new bookmark to that machine. Now I don't have to email myself links all the time.

Microsoft Announces 2006 Target Date for Windows "Longhorn"

Microsoft Announces 2006 Target Date for Broad Availability Of Windows "Longhorn" Client Operating System

Microsoft has had to re-prioritize many many times in the past several months, deciding which features will be included in the upcoming release of Windows. Longhorn (the code name for the next version of Windows) was supposed to be one of the most aggressive releases Microsoft has released; and it still might be, just not quite as feature laden as initially planned.

One of the biggest changes will be the new file system, WinFS. The current Windows file system, NTFS, is quickly becoming outdated by other operating systems (Linux, Novel, etc.). Another key feature will be the integrated mobile device support. As a huge proponent of Pocket PCs, I think this is a great move by Microsoft. One of the biggest complaints users of Pocket PCs have is the syncronization software called Activesync. I'm hoping that having the mobile support integrated into the operating system will fix many of the bugs that have plagued Activesync for years.

Only two years to go ...

A more complete datediff function

One of the more common questions I receive in my Access classes is how to display the difference between two dates. VBA has always had the datediff function; however, this function only allows you to display the difference with one date part (year, month, or day – not all three together). So, instead of seeing the difference as 1 year 2 months and 3 days, you can choose one date part.

I highly recommend reading newsgroups for valuable information about programming (use msnews.microsoft.com if you don’t have a news server available). Microsoft MVPs (most valuable professionals) constantly post and answer questions in these groups. Douglas Steele is one of the MVPs which posts quite often and has some very useful code samples on his website.

One of the functions, posted here, lets you calculate a "precise" difference between two date/time values.

You specify how you want the difference between two date/times to be calculated by providing which of ymdhns (for years, months, days, hours, minutes and seconds) you want calculated.

For example:

?Diff2Dates("y", #06/01/1998#, #06/26/2002#)
4 years
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#)
4 years 25 days
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
4 years 0 months 25 days
?Diff2Dates("d", #06/01/1998#, #06/26/2002#)
1486 days

?Diff2Dates("h", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours
?Diff2Dates("hns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours 47 minutes 33 seconds
?Diff2Dates("dhns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
1 day 18 hours 47 minutes 33 seconds

?Diff2Dates("ymd",#12/31/1999#,#1/1/2000#)
1 day
?Diff2Dates("ymd",#1/1/2000#,#12/31/1999#)
-1 day
?Diff2Dates("ymd",#1/1/2000#,#1/2/2000#)
1 day

'***************** Code Start **************
Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As Date, _
Optional ShowZero As Boolean = False) As Variant
'Author: © Copyright 2001 Pacific Database Pty Limited
' Graham R Seach MCP MVP gseach@pacificdb.com.au
' Phone: +61 2 9872 9594 Fax: +61 2 9872 9593
' This code is freeware. Enjoy...
' (*) Amendments suggested by Douglas J. Steele MVP
'
'Description: This function calculates the number of years,
' months, days, hours, minutes and seconds between
' two dates, as elapsed time.
'
'Inputs: Interval: Intervals to be displayed (a string)
' Date1: The lower date (see below)
' Date2: The higher date (see below)
' ShowZero: Boolean to select showing zero elements
'
'Outputs: On error: Null
' On no error: Variant containing the number of years,
' months, days, hours, minutes & seconds between
' the two dates, depending on the display interval
' selected.
' If Date1 is greater than Date2, the result will
' be a negative value.
' The function compensates for the lack of any intervals
' not listed. For example, if Interval lists "m", but
' not "y", the function adds the value of the year
' component to the month component.
' If ShowZero is True, and an output element is zero, it
' is displayed. However, if ShowZero is False or
' omitted, no zero-value elements are displayed.
' For example, with ShowZero = False, Interval = "ym",
' elements = 0 & 1 respectively, the output string
' will be "1 month" - not "0 years 1 month".

On Error GoTo Err_Diff2Dates

Dim booCalcYears As Boolean
Dim booCalcMonths As Boolean
Dim booCalcDays As Boolean
Dim booCalcHours As Boolean
Dim booCalcMinutes As Boolean
Dim booCalcSeconds As Boolean
Dim booSwapped As Boolean
Dim dtTemp As Date
Dim intCounter As Integer
Dim lngDiffYears As Long
Dim lngDiffMonths As Long
Dim lngDiffDays As Long
Dim lngDiffHours As Long
Dim lngDiffMinutes As Long
Dim lngDiffSeconds As Long
Dim varTemp As Variant

Const INTERVALS As String = "dmyhns"

'Check that Interval contains only valid characters
Interval = LCase$(Interval)
For intCounter = 1 To Len(Interval)
If InStr(1, INTERVALS, Mid$(Interval, intCounter, 1)) = 0 Then
Exit Function
End If
Next intCounter

'Check that valid dates have been entered
If Not (IsDate(Date1)) Then Exit Function
If Not (IsDate(Date2)) Then Exit Function

'If necessary, swap the dates, to ensure that
'Date1 is lower than Date2
If Date1 > Date2 Then
dtTemp = Date1
Date1 = Date2
Date2 = dtTemp
booSwapped = True
End If

Diff2Dates = Null
varTemp = Null

'What intervals are supplied
booCalcYears = (InStr(1, Interval, "y") > 0)
booCalcMonths = (InStr(1, Interval, "m") > 0)
booCalcDays = (InStr(1, Interval, "d") > 0)
booCalcHours = (InStr(1, Interval, "h") > 0)
booCalcMinutes = (InStr(1, Interval, "n") > 0)
booCalcSeconds = (InStr(1, Interval, "s") > 0)

'Get the cumulative differences
If booCalcYears Then
lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - _
IIf(Format$(Date1, "mmddhhnnss") <= Format$(Date2, "mmddhhnnss"), 0, 1)
Date1 = DateAdd("yyyy", lngDiffYears, Date1)
End If

If booCalcMonths Then
lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - _
IIf(Format$(Date1, "ddhhnnss") <= Format$(Date2, "ddhhnnss"), 0, 1)
Date1 = DateAdd("m", lngDiffMonths, Date1)
End If

If booCalcDays Then
lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - _
IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
Date1 = DateAdd("d", lngDiffDays, Date1)
End If

If booCalcHours Then
lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _
IIf(Format$(Date1, "nnss") <= Format$(Date2, "nnss"), 0, 1)
Date1 = DateAdd("h", lngDiffHours, Date1)
End If

If booCalcMinutes Then
lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _
IIf(Format$(Date1, "ss") <= Format$(Date2, "ss"), 0, 1)
Date1 = DateAdd("n", lngDiffMinutes, Date1)
End If

If booCalcSeconds Then
lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
Date1 = DateAdd("s", lngDiffSeconds, Date1)
End If

If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", " year")
End If

If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
If booCalcMonths Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffMonths & IIf(lngDiffMonths <> 1, " months", " month")
End If
End If

If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
If booCalcDays Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffDays & IIf(lngDiffDays <> 1, " days", " day")
End If
End If

If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
If booCalcHours Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffHours & IIf(lngDiffHours <> 1, " hours", " hour")
End If
End If

If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
If booCalcMinutes Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffMinutes & IIf(lngDiffMinutes <> 1, " minutes", " minute")
End If
End If

If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
If booCalcSeconds Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffSeconds & IIf(lngDiffSeconds <> 1, " seconds", " second")
End If
End If

If booSwapped Then
varTemp = "-" & varTemp
End If

Diff2Dates = Trim$(varTemp)

End_Diff2Dates:
Exit Function

Err_Diff2Dates:
Resume End_Diff2Dates

End Function
'************** Code End *****************

Check out Doug's homepage for more great samples.

Lebans Holdings Website

I'm always looking for more resources online for examples and plugins for Access. Lebans has been one of the most invaluable resources I have found. Well known on the discussion forums for having useful addins/funtions like MouseWheel, MonthCalendar and Justification, the website is definitely worthy of your time to explore to see what they have available.

Lebans Holdings

Crystal Reports: Field Clipping

It seems that every new version of Crystal Reports changes how data is displayed when the field simply isn't wide enough. Until Crystal 7, the data was just clipped by the size of the field on the report. So, you could spend a pretty significant amount of time trying to figure out why the numbers on your report were wrong, only to find out that the field just needed to be made wider.

Crystal 7 introduced a new option that you could finally set to show pound signs when a number was too wide, instead of just dropping the extra digits. Unfortunately, Crystal also set this option, called "Field Clipping", off by default. Even the newer versions of Crystal, 9 and 10, still have this option set to off.

To turn off field clipping and display pound signs when a number is too wide:

File > Options
Fields Tab
Number Button (you should also do the Currency Button)
Number Tab
Customize Button
Take out the check mark in the lower left for "Field Clipping".

Only new reports will be affected. Any existing reports will still have field clipping until you change the numeric and currency fields individually. To change an individual field right-click on the field and select "Format Field". Then click the "Customize" button and take out the checkmark for "Field Clipping".

Should Microsoft be worried about Google's success?

As Goolge expands into more and more realms of the internet (gmail, froogle, google local, etc.), the author of this article seems to think Google has big plans for your desktop. A very interesting read ...

Bad Hair Day

dataDog got a haircut today. Here's a picture of him after the carnage was over. 

Got Gmail?

Heard of Gmail yet? It's Google's free email service which allows you to store 1 gig - yes that is 1,000,000 bytes - of email!! No more running out of space because your friend just emailed you 10 mb worth of vacation pictures you don't care to see.

The only problem is, as I posted in a previous blog, Gmail is still in beta (testing) and you can only get an account if you get "invited." I still have a few invites left that I can send out, so if you want a gmail account, email me!.

Updated Amazon Browser Buddy Toolbar

A9

Ever wanted an easier way to find books (or any other item) at Amazon?

Amazon has finally released a new version of there award winnig search engine - complete with a browser buddy toolbar. Definitely worth checking out.

More Blogs!!

The honorable Celeste Sartor, colleague and very good friend of mine, has also recently started a blog. Definitely worth checking out! Drop her some comments, and tell her I sent you :)

Calculating the first and last day of the month

Ever needed to calculate the first and last day of the month? Here's an expression that can be used in Access:

DateSerial(Year(Now), Month(Now) + 1, 0) - Last day of month
DateSerial(Year(Now), Month(Now), 1) - First day of month

Commonly used naming conventions

When building Access databases, it is highly recommended that you use a naming convention to standardize how you name your objects. This will make development easier for yourself and help eliminate confusion when other developers make modifications to your database. This article outlines some commonly used naming conventions.

The best way to choose good names for software objects is to employ a naming convention or standard system. Aside from better communication among programmers, the many benefits of using a naming convention include:

• Saves time thinking up names
• Reduces the need for comments
• Helps you spot errors, by allowing visual consistency checks, similar to dimension checks on mathematical equations
• Triggers early detection of design or implementation issues
• Helps in debugging code
• Provides an identity for the project's code and participants
• Allows automated tool support for writing/checking code

General: Commonly used naming conventions

Deleting all data from an Access database

One of the questions I have had asked several times in class is how to empty a database of all existing data. You don't want to lose your tables structure, just the data. For example, you've created a new database and during the development process you have entered quite a bit of test data that needs to be removed before you release it to users. If done manually, this job can quickly become tedious. If your database has many tables, the following code will clear all the data in a hurry.


Dim ctr As Container, doc As Document, db As Database
Set db = CurrentDB()
Set ctr = db.Containiners!Tables
For Each doc in ctr.Documents
If Left$(doc.Name, 4) <> "MSys" Then 'Table is not a system table
db.Execute "DELETE [" & doc.Name & "].*" & _
"From [" & doc.Name & "];"
End If
Next doc

Recommended Sites

I have changed how I am storing my recommended sites. The goal is to share websites that I have found invaluable or simply great references. Trying to maintain a list on my blog would quickly get out of hand and be very difficult to organize. So, I have enlisted the services of Furl.

Furl is a new (still in beta) website that you can save your bookmarks to and then share them with friends. You can even subscribe to my bookmarks using their RSS feed (very cool!). If you want to know more about how to use RSS, search google or go to Furl's website for more information.

So, now when you click on Recommend Sites, you will be taken to my bookmarks page on Furl. There, you will be able to filter and view by category, popularity, rating and more. Hopefully you will all find that very useful!

Firefox browser to hit milestone

Firefox browser to hit 1.0 milestone | CNET News.com



My favorite browser is growing up! If you haven't tried Firefox yet, now's the time. The usability features (tabbed browsing, integrated search bar, extensions, skins, etc.) that Firefox introduces are what make it my browser of choice.

I have found Firefox to be more stable, faster, and easier to use than Internet Explorer. Try it out and let me know what you think!

Gmail tools and addins

Plugins and tools to make Gmail easier to use are starting to pop up on the web, click read more to see some of the most popular. Don't know what Gmail is? Click read more. Props to Rob for the original post.


* Official Gmail Notifier from Google (beta) - Downloadable Windows application that alerts you when you have new Gmail messages.
* Gmail Search Extension - Search your Gmail account from the Firefox search bar
* Gmail Tray - A search box for the Windows taskbar
* Gmail Notifier - Firefox extension, installs an icon on your toolbar that displays the amount of new messages in your inbox (previous post)
* Gmail-to - Associate all mailto: links with Gmail as opposed to Outlook
* Gmail-to Bookmarklet - Compose messages from your Gmail account from any page (previous post)
* Gmailit - Bookmarklet to send the current page you're viewing to someone through Gmail
* GMail Loader (GML) - Cross platform application forwards your existing email to Gmail
* Pop Goes the GMail - Gmail > POP3 converter
* gExodus - Graphical user interface tool allows you to import your emails into your Gmail account.
* GMail, RSS Feeds - Retrieve notifications of new mail through RSS
* Gmail Gems - Tips and Tricks to use Gmail more efficiently
* Unread Gmail - Create a label called "Unread", Gmail will automatically add all unread items to it (Via Gmail Wiki)
* GSuite - Lets you access and use Gmail from a mini-browser in the system tray
* Gmail icon generator - Create an image of your Gmail address to minimize spam exposure (Via here)

Gmail is Google's new email service. It's been a pretty hot topic on many technology news sites lately. Unfortunately, it's still in beta, which means it isn't open to the public just yet. Existing users are given "invitations" so they can give friends a gmail account. I guess that's Google's way of doing a controlled rollout of Gmail. Anyway, if you really want one and don't have one yet, shoot me an email. I may just have an invitation I can send you.

Blog separation

I've decided to go with two blogs. This blog will mainly be for technology related information while paqguy remains my personal blog.