Wednesday, November 28, 2007

Removing External Data References from Excel

I really enjoy working with both Excel and Access when trying to solve quick data collection issues.

However, sometimes after you caputre the data inside of the Excel Spreadsheet from Microsoft Access you want to freeze the data in Excel.

Here is the fastest way to drop all the data links to external resources from inside of Microsoft Excel to create a snap shot in time.

Sub Delete_All_Queries()

Dim myQueryTables As QueryTable

Dim myWorkSheets As Worksheet

For Each myWorkSheets In ThisWorkbook.Worksheets

For Each myQueryTables In myWorkSheets.QueryTables
myQueryTables.Delete
Next myQueryTables
Next myWorkSheets

End Sub

Monday, November 5, 2007

Microsoft CDONTS now CDO.Messaging (Windows XP, 2000, 2003)

Standard ASP using email messaging in Windows 2003 R2 Servers

I ran into a snag the other day when I installed my new 2003 R2 servers and my email messaging quite working. After review discovered CDONTS.NEWMAIL is no longer supported in 2003 R2 Servers and you now need to understand CDO.Message.

Sending email with CDO is actually a simple process. You create a reference to the new CDO component and while taking into consideration the new command syntax. The challenge was trying to find the new syntax formats in ASP format vs ASP.NET. The resources seemed to have disappeared on using this in ASP because 2003 R2 is now ASP.NET.

I really do not have time to convert to ASP.NET so this is what I found to get everything moving again using ASP in a Windows 2003 R2 environment.

The basic conversion, the old CDONTS.NEWMAIL to CDO.MESSAGE.

Old Code Format:
Set CDONTS = Server.CreateObject("CDONTS.NEWMAIL")
With CDONTS
.Value("From") = "Michael Kintner "
.Value("To") = "Michael Kintner "
.Subject = "EMail Subject"

.Body = "EMail Body In HTML Format"
.MailFormat = 0 ' Use HTML Format
.BodyFormat = 0 ' Use HTML Format
.AttachFile "c:\myfile.txt"
.Send
End With
Set CDONTS = Nothing

New Code Format:
Set cdoMessage = CreateObject("CDO.Message")
With cdoMessage
.From = "Michael Kintner "
.To = "Michael Kintner "
.Subject = "EMail Subject"
.HTMLBody = "EMail Body

In HTML Format
"


'.TextBody = "EMail Body" & CRLF & "In Test Format"
.AddAttachment "c:\myfile.txt"
.Send
End With
Set cdoMessage = Nothing

Note: When sending email via HTML format then use .HTMLBody property, if via TEXT format then use the .TextBody property which has been commented.

To send an email via standard text:
Set cdoMessage = CreateObject("CDO.Message")
With cdoMessage
.From = "Michael Kintner "
.To = "Michael Kintner "

.Bcc = "Michael Kintner "
.Cc = "Michael Kintner "
.Subject = "EMail Subject"
.TextBody = "EMail Body"
.Send
End With
Set cdoMessage = Nothing



To send an email via HTML text:
Set cdoMessage = CreateObject("CDO.Message")
With cdoMessage
.From = "Michael Kintner"
.To = "Michael Kintner"
.Bcc = "Michael Kintner "
.Cc = "Michael Kintner "

.Subject = "EMail Subject"
.HTMLBody = "EMail Body This is a test HTML body"
.Send
End With
Set cdoMessage = Nothing

When using these basic examples to uses the local STMP Server embedded into IIS to send your email. However if you wish to use an outside mail server use this format.

Set cdoMessage = CreateObject("CDO.Message")
With cdoMessage
.From = "Michael Kintner "
.To = "Michael Kintner "
.Bcc = "Michael Kintner "
.Cc = "Michael Kintner "

.Subject = "EMail Subject"
.TextBody = "EMail Body"
.Send
End With
Set cdoMessage = Nothing

Monday, October 8, 2007

In the beginning

This is my first blog post. Testing to see how well this works.