CODEWITHSUNDEEP
vbaoutlook
Pred05
Pred05

Reputation: 542

How to store data from a file in memory for reuse?

I have VBA code which executes on mail's reception.

I want to forward a template to the first address found in the mail. I execute a regex to find the email address in the mail, read a html file (the template) and forward it to the email address.

Outlook shuts down after few minutes. I think it is a performance problem. I want to optimize the code and if I can between two executions not read the template two times. Is it possible to store it into a global variable?

Sub GetEmailAndForward(Item As Outlook.MailItem)

' RegExp
Dim mailRegExp As RegExp

' File
Dim FileTemplate As Integer
Dim FileProperties As Integer

' Properties
Dim splitProperty() As String

' Email
Dim DataLine As String
Dim emails As MatchCollection
Dim email As String
Dim forward As Outlook.MailItem
Dim body As String
Dim forwardText As String

' Path
Dim fileTemplatePath As String
Dim dirPath As String
Dim filePropertyPath As String

dirPath = "C:\OutlookVBA"

Set mailRegExp = New RegExp

With mailRegExp
    .Pattern = "[\_]*([a-z0-9]+(\.|\_*)?)+@([a-z][a-z0-9\-]+(\.|\-*\.))+[a-z]{2,6}"
    .Global = False
    .IgnoreCase = True
End With

' Get the template
fileTemplatePath = dirPath & "\template.html"

' Get the email body to analyse
body = Item.body

' Get the first email found
If mailRegExp.Test(body) Then
    Set emails = mailRegExp.Execute(body)
    If emails.Count > 0 Then
        email = emails.Item(0)
       
        Set forward = Item.forward

        FileTemplate = FreeFile()
        Open fileTemplatePath For Input As #FileTemplate
   
        While Not EOF(FileTemplate)
            Line Input #FileTemplate, DataLine
            forwardText = forwardText & DataLine
        Wend

        forward.BodyFormat = olFormatHTML
        forward.HTMLBody = forwardText & forward.HTMLBody

        Close #FileTemplate

        If Not IsEmpty(email) Then
            forward.Recipients.Add email
            forward.subject = "RE:" & Item.subject
            forward.Send
        End If
    End If
End If
End Sub

Upvotes: 0

Views: 349

Answers (1)

Tim Williams
Tim Williams

Reputation: 166391

You can use something like this - the function will only read from the file on the first call, and after that will use the text stored in the static variable:

Function GetForWardText(f As String) As String

    Static rv As String '<< valuje is maintained between calls

    If Len(rv) = 0 Then
        rv = CreateObject("scripting.filesystemobject"). _
                  opentextfile(f, 1).readall()
    End If

    ForWardText = rv

End Function

In your code, remove this: 

    FileTemplate = FreeFile()
    Open fileTemplatePath For Input As #FileTemplate

    While Not EOF(FileTemplate)
        Line Input #FileTemplate, DataLine
        forwardText = forwardText & DataLine
    Wend

and replace with:

    forwardText = GetForWardText(fileTemplatePath)

Upvotes: 1

Related Questions