Baffled by loss of data when emailing a workbook

[Pauljj]

Morning all,

I have set up a form which users fill in and then click on a button to send the document by email, problem being when the email is opened up none of the data they entered appears on the document, for the life of me I cannot understand why, as there is never a need to save the document

I add the 2 bits of code below, can anyone spot something I've missed please

Sub Mail_workbook_1()

    Dim app As Integer
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtstr As String
    Dim FileFormatNum As Long
    Dim dest As Worksheet
Application.Calculation = xlCalculationAutomatic


'Check applicable option has been selected

app = Range("B71")

If app < 3 Then

MsgBox "Please ensure you have indicated whether all 3 target bands are applicable or not", vbCritical, "Targets Submission"

Exit Sub

End If

        
: AutoUpload

    
    
'Range("C26, F26, I26").ClearContents
'
'Range("B30:C35, E30:F35, H30:I35").ClearContents

'With ActiveWorkbook
'
'    .Close savechanges:=False
'
'End With
'


End Sub

The emailing code is

Dim objNotesSession As Object
Dim objNotesMailFile As Object
Dim objNotesDocument As Object
Dim objNotesField As Object

Function AutoUpload()

On Error GoTo SendMailError


Set objNotesSession = CreateObject("Notes.NotesSession")

EMailSendTo = "weeklystats@trailfinders.com"
'EMailSendTo = "nopromotions@trailfinders.com" '' Required - Send to address
EMailCCTo = objNotesSession.UserName                 '' Optional
EMailBCCTo = "paulhai@trailfinders.com"                     '' Optional
EmailSubject = Range("B70")

''Establish Connection to Notes


''Establish Connection to Mail File
''                                    .GETDATABASE("SERVER", "FILE")
Set objNotesMailFile = objNotesSession.GETDATABASE("", "")
''Open Mail
objNotesMailFile.OPENMAIL

''Create New Memo
Set objNotesDocument = objNotesMailFile.CREATEDOCUMENT

''Create 'Subject Field'
'Set objNotesField = objNotesDocument.APPENDITEMVALUE("Subject",
'EMailSubject)

''Create 'Send To' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("SendTo", EMailSendTo)

''Create 'Copy To' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("CopyTo", EMailCCTo)

''Create 'Blind Copy To' Field
'Set objNotesField = objNotesDocument.APPENDITEMVALUE("BlindCopyTo",
'EMailBCCTo)

'Create 'Subject Field'
    Set objNotesField = objNotesDocument.APPENDITEMVALUE("Subject", EmailSubject)

''Create 'Body' of memo
Set objNotesField = objNotesDocument.CREATERICHTEXTITEM("Body")

With objNotesField
    .APPENDTEXT "This e-mail is generated by an automated process."
    .ADDNEWLINE 1
'    .APPENDTEXT "Please follow established contact procedures should you have any questions."
'    .ADDNEWLINE 2
End With

''Attach the file   --1454 indicate a file attachment
''objNotesField = objNotesField.EMBEDOBJECT(1454, "", "C:\Temp\test.xls")
objNotesField = objNotesField.EMBEDOBJECT(1454, "", ActiveWorkbook.FullName)

''Send the e-mail
objNotesDocument.Send (0)

''Release storage
Set objNotesSession = Nothing
Set bjNotesSession = Nothing
Set objNotesMailFile = Nothing
Set objNotesDocument = Nothing
Set objNotesField = Nothing

''Set return code
SendMail = True

msg = MsgBox("Your Incentive Targets Email has been sent successfully!", , "Incentive Targets Sent")


Exit Function


SendMailError:

msg = MsgBox("There has been an error, this email has not been sent" & vbCrLf & vbCrLf & " Please check you have Lotus notes open !" & vbCrLf & "Otherwise please call Systems!", vbCritical, "Incentive Targets Sending Error")

'msg = "Error # " & Str(Err.Number) & " was generated by " _
'            & Err.Source & Chr(13) & Err.Description
'MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext

SendMail = False





End Function

 

[RoryA]

If you don't save the file, there won't be any data in the disk copy which is what is being emailed.

 

[Pauljj]

If you don't save the file, there won't be any data in the disk copy which is what is being emailed.

I'm sure that I've not needed to save these in the past but I will bow to your better judgement, thank you