CODEWITHSUNDEEP
performanceexcelvbaoutlookexcel-formula
Thomas Anderson
Thomas Anderson

Reputation: 1

Excel VBA Outlook / Mail functions requires recalculation or sets all formulas to #Value

I'll try to keep it short and precise. I really hope you can help me.

I am facing the following problem:

Background:

What I want to do:

What is the problem?

  1. If I use the "Envelope" method to prepare the e-mails everything is fine until I press send. However, every time I press send excel automatically recalculates the entire Workbook. Obviously I do not want to wait 5-10 minutes to send out each e-mail (always between 10 and 20)

  2. Since I thought it might have to do with the "Envelope" method I decided to switch to creating an e-mail directly via Outlook (outlook object). It worked fine as far as opening the e-mail and sending it without recalculation. However, after the e-mail is opened by Outlook, all(!) formulas in the entire Workbook are set to #Value. This obviously also forces me to recalculate as I cannot create the table for the next person's e-mail.

Does anyone know what is causing the recalculation/error values and what I can do to stop it? I'd be really glad about any suggested solutions.

I am also attaching my code, though I doubt it will help in clearing up the issue

`'DESCRIPTION: 'This routine prepares an e-mail for requesting the progress estimates from the deliverable owners '1. set all the values based on named ranges in PI and Config sheets '2. Concatenate all relevant strings to full e-mail text '3. select PI table '4. Create e-mail and display

Sub PrepareEmail()

Dim s_EmailAddress As String, s_FirstName As String
Dim s_Email_Greeting As String, s_Email_MainText1 As String, s_Email_MainText2 As String, s_Email_DeadlineRequest As String
Dim s_Email_Deadline As String, s_Email_Subject As String, s_Email_ClosingStatement As String, s_Email_SenderName As String, s_Email_CC As String
Dim s_Email_Full As String
Dim rng_PI_TableValues As Range, rng_PI_TableFull As Range

Dim s_Email_FullText As String

Dim obj_OutApp As Object
Dim obj_OutMail As Object


s_EmailAddress = [ptr_PI_Email]
s_FirstName = [ptr_PI_FirstName]
s_Email_Subject = [ptr_Config_PIEmail_Subject]
s_Email_Greeting = [ptr_Config_PIEmail_Greeting]
s_Email_MainText1 = [ptr_Config_PIEmail_MainText1]
s_Email_MainText2 = [ptr_Config_PIEmail_MainText2]
s_Email_DeadlineRequest = [ptr_Config_PIEmail_DeadlineRequest]
s_Email_Deadline = [ptr_Config_PIEmail_Deadline]
s_Email_ClosingStatement = [ptr_Config_PIEmail_ClosingStatement]
s_Email_SenderName = [ptr_Config_PIEmail_SenderName]
s_Email_CC = [ptr_Config_PIEmail_CC]

'Concatenate full e-mail (using HTML):
s_Email_Full = _
    "<basefont face=""Calibri"">" _
    & s_Email_Greeting & " " _
    & s_FirstName & ", " & "<br> <br>" _
    & s_Email_MainText1 & "<br>" _
    & s_Email_MainText2 & "<br> <br>" _
    & "<b>" & s_Email_DeadlineRequest & " " _
    & s_Email_Deadline & "</b>" & "<br> <br>" _
    & s_Email_ClosingStatement & "," & "<br>" _
    & s_Email_SenderName _
    & "<br><br><br>"
'-------------------------------------------------------

Set rng_PI_TableValues = Range("tbl_PI_ProgressInput")
Set rng_PI_TableFull = Union(rng_PI_TableValues, Range("tbl_PI_ProgressInput[#Headers]"))

Application.EnableEvents = False
Application.ScreenUpdating = False

Set obj_OutApp = CreateObject("Outlook.Application")
Set obj_OutMail = obj_OutApp.CreateItem(0)



With obj_OutMail

       .To = s_EmailAddress
       .CC = s_Email_CC
       .Subject = s_Email_Subject
       .HTMLBody = s_Email_Full & RangetoHTML(rng_PI_TableFull)
       .Display

End With

Application.EnableEvents = True
Application.ScreenUpdating = True

Call update_Status

End Sub

`

Upvotes: 0

Views: 704

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33175

If you're using RangeToHTML from Ron de Bruin's website, that's what's causing your problem. That utility is fine if you need perfect fidelity and have a heavily formatted bu otherwise fairly simple range. But if your range has a bunch of dependencies, you'll have problems. It's putting the range into its own workbook, so any formulas that refer to data outside the range get funky.

If you need perfect fidelity, then you're stuck because the only way it will be perfect is by saving the range as HTML and reading that back. But if you don't have a bunch of heavy formatting or you just need a nice looking table, then I suggest you write your own RangeToHTML function that produces the HTML strings.

David McRitchie has some functions that do a pretty good job if you don't want to roll your own. http://dmcritchie.mvps.org/excel/xl2html.htm

Also, I don't know what update_Status does, but if it's causing a recalc, then you have two problems. If that's the case, figure out how to store up all the stuff that update_status does and do it once at the end rather than in the loop.

Upvotes: 0

Related Questions