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 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.
Code:
=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.
Code:
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