VBA Code + regx

[Nancy]

Hi,

First, I know very little about VBA.

We have recently upgraded from Access 2007 to Access 2010. A module with VBA function was written for me by a Microsoft Pro. The code exports a query and saves it on our file server as an Excel file. The code includes emailing the Excel spreadsheet to several people. This is done because the code is initated from a macro in the database that is run from a scheduled task on our file server daily.

When I copied a previous module to a new Access 2010 database and tried to run, I get the error message "Complie Error - User-defined type not defined". The error is on the code "Dim regx As New RegExp". We can no longer get help from our Microsoft Pro. Can anyone help me? The VBA code is below.

Option Compare Database
Public Function getSortFormula(formula As String)
Dim strNewFormula As String
Dim regx As New RegExp

strNewFormula = formula
If Left(strNewFormula, 1) = "C" Then

Set regx = New RegExp

regx.Pattern = "^C[0-9]*H([^egf]|.*)"
regx.Global = False
regx.IgnoreCase = False
If regx.Test(formula) Then
strNewFormula = Replace(strNewFormula, "H", "ZZZ", 1, 1, vbTextCompare)
End If

regx.Pattern = "^C[^0-9asroemfl]"
If regx.Test(strNewFormula) Then
strNewFormula = Replace(strNewFormula, "C", "C1", 1, 1, vbTextCompare)
End If



End If

getSortFormula = strNewFormula
End Function

Public Function EmailDailyMargin()
DoCmd.OutputTo acOutputQuery, "qry_Daily CGS Review", acFormatXLSX, "G:\Nancy\Task\DailyMarginReview.xlsx"

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "DailyMarginReview"
objMessage.Sender = "Nancy@strem.com"
objMessage.To = "Nancy@strem.com;"
objMessage.TextBody = "Previous work day sales margins to review. Nancy"
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...tion/sendusing") = 2
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...ion/smtpserver") = "exmail"
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...smtpserverport") = 25
objMessage.Configuration.Fields.Update
objMessage.AddAttachment "G:\Nancy\Task\DailyMarginReview.xlsx"
objMessage.Send
End Function

[orange]

In your references , make sure you include the
Microsoft VBScript Regular Expressions 5.5 <----- the later versions may be different (it may not be 5.5)

[Nancy]

This fixed the problem! Thank you very much.