Remove visible command button when attaching in outlook

[mfiltvedt84]

See this code.
This works but Im trying to make the command button im pushing in the sheet, not visible when the sheet has been converted to pdf and attached in outlook. How do I remove the visible command button in the pdf..???

Private Sub CommandButton1_Click()
' Create PDF of active sheet and send as attachment.
'
Dim strPath As String, strFName As String
Dim OutApp As Object, OutMail As Object

'Create PDF of active sheet only
strPath = Environ$("temp") & "\" 'Or any other path, but include trailing "\"

strFName = ActiveWorkbook.Name
strFName = Left(strFName, InStrRev(strFName, ".") - 1) & "_" & ActiveSheet.Range("D7") & ".pdf"



ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
strPath & strFName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

'Set up outlook
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

'Create message
On Error Resume Next
With OutMail
.to = Range("L13") 'Insert required address here ########
.CC = ""
.BCC = ""
.Subject = "Interiør solskjerming Tilbud"
.Body = ""
.Attachments.Add strPath & strFName
.Display 'Use only during debugging ##############################
'.Send 'Uncomment to send e-mail ##############################
End With

'Delete any temp files created
Kill strPath & strFName
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

[trevorc]

This ones easy,
Right click the command button and select properties, there should be an option to print/not print the command button, untick it and there you go.

[mfiltvedt84]

Thanks, the button is gone when the attachment is in outlook, but the button goes away also in the template sheet im working in.... how not to remove the button there too?? I wanna continue work in the sheet..

[trevorc]

Originally Posted by mfiltvedt84

Thanks, the button is gone when the attachment is in outlook, but the button goes away also in the template sheet im working in.... how not to remove the button there too?? I wanna continue work in the sheet..

The only thing that it should have done is stop the button being shown in a print preview, please provide a sample, and I'll have go at helping you again.
regards
Trevor

[mfiltvedt84]

I have a original template file where im typing in data for send offers on products to customers. I have made a button on this template that runs this code under here.
The code makes av saveascopy file on a path on onedrive, but also opens outlook and attachd this excel file in converted pdf. But the button is shown in the pdf that has been converted and on the excel copy file that has been placed on onedrive. I want to keep the button on the template because im gonna send more offers to other customers. See?
But the button wont go away on the pdf or the copied sheet on onedrive.
-----------------------------

Private Sub CommandButton1_Click()
' Create PDF of active sheet and send as attachment.
'
Dim strPath As String, strFName As String
Dim OutApp As Object, OutMail As Object


'Create PDF of active sheet only
strPath = Environ$("temp") & "\" 'Or any other path, but include trailing "\"

strFName = ActiveWorkbook.Name
strFName = Left(strFName, InStrRev(strFName, ".") - 1) & "_" & ActiveSheet.Range("D7") & ".pdf"
ActiveWorkbook.SaveCopyAs Filename:="C:\Users\Marius Filtvedt\OneDrive - Omega Industrier AS\1. OMEGA\Omega Østfold\Tilbud kunder\Tilbud Interiør" & "_" & ActiveSheet.Range("D7") & ".xls"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
strPath & strFName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

'Set up outlook
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

'Create message
On Error Resume Next
ActiveSheet.Shapes("CommandButton1").Delete

With OutMail
.to = Range("L13") 'Insert required address here ########
.CC = ""
.BCC = ""
.Subject = "Interiør solskjerming Tilbud"
.Body = ""
.Attachments.Add strPath & strFName
.Display 'Use only during debugging ##############################
'.Send 'Uncomment to send e-mail ##############################
End With

'Delete any temp files created
Kill strPath & strFName
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

[mfiltvedt84]

Or doing it another way, how to remove the macroes when the fil in savecopyas has been done?? The file size in savecopyas is almost 3 timers larger with macro...

[trevorc]

Originally Posted by mfiltvedt84

Or doing it another way, how to remove the macroes when the fil in savecopyas has been done?? The file size in savecopyas is almost 3 timers larger with macro...

Try setting the visible property of the button to false, before creating the pdf then set it to true after that is done.

[trevorc]

I should have added that you can use the recorder when you create the command button, to see the VBA code to use for turning the visible property on/off in your code.

[mfiltvedt84]

Originally Posted by trevorc

I should have added that you can use the recorder when you create the command button, to see the VBA code to use for turning the visible property on/off in your code.

Ok.

Another problem.
When om saving my file with savecopyas the Template im saving can sometimes be to the same customer but different offer. How will my code look like to add a -1 or something on filename when saving...? Where to put it? When everytime I write offer to the same customer it shallautomatically add 1, then 2 and so on.. help

[trevorc]

Originally Posted by mfiltvedt84

Ok.

Another problem.
When om saving my file with savecopyas the Template im saving can sometimes be to the same customer but different offer. How will my code look like to add a -1 or something on filename when saving...? Where to put it? When everytime I write offer to the same customer it shallautomatically add 1, then 2 and so on.. help

You already have your answer above, this is what you do for that as well. Keep a record on your sheet of the last used number and add this to the file name as you have done for whatever you have in D7... & ActiveSheet.Range("D7") &

[mfiltvedt84]

Originally Posted by trevorc

You already have your answer above, this is what you do for that as well. Keep a record on your sheet of the last used number and add this to the file name as you have done for whatever you have in D7... & ActiveSheet.Range("D7") &

But how does I do it? How will the code look like?

[trevorc]

ActiveWorkbook.SaveCopyAs Filename:="C:\Users\Marius Filtvedt\OneDrive - Omega Industrier AS\1. OMEGA\Omega Østfold\Tilbud kunder\Tilbud Interiør" & "_" & ActiveSheet.Range("D7") & ActiveSheet.Range("XX1") & ".xls"

XX1 or whatever holds your file number