VBA Code when sending email with PDf attachements dletes textbox from sheet

[Mcfg007]

I am struggling to understand how at times, not all the time, when I press the button to send an email with a PDF of the sheet required, it deletes the active textbox of the sheet. It is not all the time.

Can anyone share some help on this?

This is the vba code I am using:

Sub AttachActiveSheetPDF()
Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String
Dim OutlApp As Object

' Not sure for what the Title is
Title = Range("A1")

' Define PDF filename
PdfFile = "JCIS Email Request for ......"
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"

' Export activesheet as PDF
Dim currentSheet As Worksheet
With ActiveWorkbook
Set currentSheet = .ActiveSheet
.Worksheets(Array("Finger Prints")).Select
.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With

' Use already open Outlook if possible
On Error Resume Next
Set OutlApp = GetObject(, "Outlook.Application")
If Err Then
Set OutlApp = CreateObject("Outlook.Application")
IsCreated = True
End If
OutlApp.Visible = True
On Error GoTo 0

' Prepare e-mail with PDF attachment
With OutlApp.CreateItem(0)

' Prepare e-mail
.Subject = "subject"
.To = "email@aa.com"
.CC = "email@aa.com"
.Body = "Dear sir," & vbLf & vbLf _
& "Please find attached a ......" & " " & Sheets("Case Details").Range("D9") & vbLf & vbLf _
& "Regards," & vbLf _
& Application.UserName & vbLf & vbLf
.Attachments.Add PdfFile

' Try to send
On Error Resume Next
.Send
Application.Visible = True
If Err Then
MsgBox "E-mail was not sent", vbExclamation
Else
MsgBox "E-mail successfully sent", vbInformation
End If
On Error GoTo 0

End With

' Delete PDF file
Kill PdfFile

' Quit Outlook if it was created by this code
If IsCreated Then OutlApp.Quit

' Release the memory of object variable
Set OutlApp = Nothing

End Sub

 

[cerfani]

Not sure if you mean the created pdf is missing the textbox or if the macro actually deletes the textbox from the sheet. Maybe onm some files they are forms textboxes and other files they are activex and only one would work... just a guess

 

[Mcfg007]

Not sure if you mean the created pdf is missing the textbox or if the macro actually deletes the textbox from the sheet. Maybe onm some files they are forms textboxes and other files they are activex and only one would work... just a guess

The PDF is created and email is sent, but at occasions before the email is sent the active textbox on the sheet gets deleted and is not included in the PDF on the mail either.

 

[cerfani]

OK so the control itself is physically deleted from the sheet, did you identify which line of code deletes the textbox? Step through it

 

[Mcfg007]

No, that's the thing I am not sure which parts does it. It does not happen all the time.

I have a button on the main sheet command an email to be sent of another sheet in pdf format. When I press that button it does send the email but at times deletes the active textbox on the sheet from which the PDF file is created from. I can not see what part of the code is causing this.

 

[cerfani]

Well you will need to identify a file it happens on... Computer errors can feel random but they are not. You need to identify a file the error happens on and then place a breakpoint in the code and step through it. You need to debug it so you can identify the problem. I literally have no experience with this issue but if I had to guess... you have ActiveX textboxes on some files and windows forms textboxes on other files.... they look and act the same to you but to the code that creates a pdf image it does not recognize the textbox since there are two textbox classes you can use in vba.

Just a guess i could be way off but you can get more information and perhaps solve the problem yourself or post more info here.

 

[Mcfg007]

Yes, I have not identified while sheet it happens to and I have checked the VBA code which appears to be the same as the other two I have setup also to send emails and do not delete the sheet. I have check the structure of the ActiveX Textbox and it appears to have the same settings as the other ones.

It seams only at the second time I email one of the sheets. I have 3 sheets lets call it sheet1, sheet2 and sheet3.

The first time I press one of the 3 button to email a sheet all goes fine, second time sheet 2 always get the activex textbox deleted. I have notice that seams to be an issue between sheet2.

Is there anyone there that could share more light into this problem?

 

[cerfani]

Two classes may share the same properties but in memory might be stored differently...

Example...

Class Textbox
{
    int Length;
    string Value;
}

Class Textbox2
{
    string Value;
    int Length;
}

will not be stored in memory the same way... both of those properties take up 4 bytes to save except in one class Length is at base + 0 and the other class the Length is at base + 4 so they are different classes a class is a memory blueprint, them having the same setting is not relevant to the layout in memory

 

[Mcfg007]

Two classes may share the same properties but in memory might be stored differently...

Example...

Class Textbox
{
    int Length;
    string Value;
}

Class Textbox2
{
    string Value;
    int Length;
}

will not be stored in memory the same way... both of those properties take up 4 bytes to save except in one class Length is at base + 0 and the other class the Length is at base + 4 so they are different classes a class is a memory blueprint, them having the same setting is not relevant to the layout in memory

Thanks for your reply but what do you actually mean? I am quite new on this?

 

[Mcfg007]

Can anyone help me with this problem?