Baffled by loss of data when emailing a workbook

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,044
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

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

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

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,573
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you don't save the file, there won't be any data in the disk copy which is what is being emailed.
 

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,044
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
 

Forum statistics

Threads
1,144,680
Messages
5,725,753
Members
422,639
Latest member
i have a question

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top