Lotus attachment doesn't show Excel formula

[Cjaye]

I am sending out emails (Lotus 8.5.2) with Excel spreadsheet attachments (Excel 2003) using vba code (thank you Ron De Bruin). The process has worked great, but now I want to add another spreadsheet column that will contain a formula that I want to be included in the attachment the email recipient receives (i.e., the recipient opens the attachment and enters their vehicle mileage into the spreadsheet and the conditional formula would display an error based on their input). With my current code, the formula does not come across with the attachment, only the actual error message that was in the spreadsheet when it was attached. Confusing, I know. Let me try again. Here is the formula I want to be included in the attachment:

=IF(ISBLANK(D2),"ERROR, mileage not reported", IF(D2=" ","ERROR, mileage not reported", IF(D2=C2,"Mileage is the same as last month, please reverify", IF(D2<C2,"MILEAGE report?, months last to compared low too is  IF(D2>(C2+9999),"Mileage is too high, it is over 9999 miles compared to last months report", " ")))))

If for example D2 is empty (column C has previous mileage, column D has current mileage), the formula would cause "ERROR, mileage not reported" to be displayed in E2. The problem is...only the message is what shows up in the email recipient's attachment (column E) and although it is still an accurate message for the recipient to see, I want the formula to also be there because the recipient will be asked to enter their mileage into the spreadsheet (and return to us) and the formula is supposed to alert them with the messages if the mileage they enter is wrong in some way.

Here is the section of my code that does the sending. Was wondering if my problem had to do with the CreateRichTextItem of > Set obAttachment = noDocument.CreateRichTextItem("stAttachment"). I'm a novice at vba code..so any help/direction would be very much appreciated.

Public Sub SendEmail(ByVal pEmail As String, ByVal pEFN As String, ByVal pNotifType As String, pNotification As String, ByVal pPOC As String)
'Inherits System.Windows.Forms.RichTextBox
  Dim noSession As Object, noDatabase As Object, noDocument As Object
  Dim obAttachment As Object, EmbedObject As Object
  Dim stSubject As Variant, stAttachment As String
  Dim vaRecipient As Variant, vaMsg As Variant
  Dim Password As String
  Dim CurDate
  CurDate = Date
  Dim LDate As String
 
  LDate = (MonthName(DatePart("m", CurDate))) & ", " & DatePart("yyyy", CurDate)
 
  Const EMBED_ATTACHMENT As Long = 1454
  Const stTitle As String = "Active workbook status"x
  Const stMsg As String = "The active workbook must first be  saved " & vbCrLf _
  & "before it can be sent as an attachment."
  Do
    vaRecipient = pEmail
   Loop While vaRecipient = ""
   'If the user has canceled the operation.
   If vaRecipient = False Then Exit Sub
 
     If pNotification = "Final mileage due to Govt" Then
       vaMsg = "Removed for the message board " & LDate & vbCrLf & vbCrLf
 
     ElseIf pNotification = "Gas cutoff notification" Then
       vaMsg = "Removed for the message board" & vbCrLf & vbCrLf
 
     ElseIf pNotification = "Third notice" And pNotifType <> "PM service due notification" Then
       vaMsg = "Removed for the message board, " & vbCrLf & vbCrLf _
     Else
       'First and second notices (third notice is above)
     Do
       vaMsg = "Removed for the message board, " & vbCrLf & vbCrLf _
 
     Loop While vaMsg = ""
     End If
 
     'If the user has canceled the operation.
     If vaMsg = False Then Exit Sub
     Do
       stSubject = pEFN
     Loop While stSubject = ""
     stAttachment = ActiveWorkbook.FullName
 
     'Instantiate the Lotus Notes COM's  Objects.
     Set noSession = CreateObject("Notes.NotesSession")
     Set noDatabase = noSession.GETDATABASE("", "")
 
     'If Lotus Notes is not open then open the mail-part of it.
     If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
 
     'Create the e-mail and the attachment.
     Set noDocument = noDatabase.CreateDocument
     Set obAttachment = noDocument.CreateRichTextItem("stAttachment")
 
     Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)
 
     'Add values to the created e-mail main properties.
     With noDocument
      .Form = "Memo"
      .SendTo = vaRecipient
      .Subject = stSubject
      .Body = vaMsg
      .SaveMessageOnSend = True
     End With
     'Send the e-mail.
     With noDocument
      .PostedDate = Now()
      On Error GoTo Err_Handle
      .Send 0, vaRecipient
     'Release objects from the memory.
      Set EmbedObject = Nothing
      Set obAttachment = Nothing
      Set noDocument = Nothing
      Set noDatabase = Nothing
      Set noSession = Nothing
 
      Exit Sub
Err_Handle:
      If vaRecipient = " " Then
        MsgBox pPOC & " has no email address in column L of your Master spreadsheet"
      Else
        MsgBox " Either the Group is setup wrong in this program for " & pPOC & " or something else is going on.  Alert Catherine"
      End If
     End With
 
   'Release objects from the memory.
    Set EmbedObject = Nothing
    Set obAttachment = Nothing
    Set noDocument = Nothing
    Set noDatabase = Nothing
    Set noSession = Nothing
 
End Sub

 

[Cjaye]

Since I haven't had any replies to my request for help, I'm wondering if I didn't explain my problem well enough or overwhelmed with too much superfluous info. Basically, what I want to be able to do is to modify an already working process of mass emailing a spreadsheet using Lotus notes, by including an Excel formula in the spreadsheet that is emailed. So the recipient should be able to open the attached spreadsheet in their email, enter some data and see the alert that the formula provides based on their input. How do you pass a formula using the mass emailing technique I used above?

 

[Cjaye]

Re: Lotus attachment doesn't show Excel formula - PROBLEM SOLVED

Problem solved. I assumed the problem had to do with the vba code used to pass the attachment and the variables (string?, objects? I don't have a real good grasp of this). But the problem instead was...simply related to the vba code that copies the data to the temp worksheet that is used for the attachment. What was needed, was to include the PasteSpecial code for pasting formulas in addition to the Paste Values and Paste Formats. Here's an excerpt.

       'Copies the data from the rng variable to the new email workbook
        rng.Copy
        With EmailWB.Sheets(1)
          .Cells(1).PasteSpecial Paste:=8
          .Cells(1).PasteSpecial Paste:=xlPasteValues
          .Cells(1).PasteSpecial Paste:=xlPasteFormats
          .Cells(1).PasteSpecial Paste:=xlPasteFormulas  '12/13/2011 added so when the recipient opens attachment they can see the formula doing it's job based on the user's cell input
          .Cells(1).Select
          Application.CutCopyMode = False
        End With