Omit Form Control from printing in copy/paste into email

[bvanscoy678]

Hi,


I have a very simple piece of code that copy/paste a range into a body of an email. It works just fine with the exception of the form control. I have a combo box that overlays E3 and it populates the value in E3. I don't want to see both the value in E3 and the combo box as it is showing me now.


https://www.dropbox.com/s/xr3hvm7d7un4be4/EE%20MAC%20and%20EMAIL%20address.xlsm

Thanks! Brent

[pre]

'
Sub CreateMail()

Dim rngSubject As Range
Dim rngTo As Range
Dim rngBody As Range
Dim objOutlook As Object
Dim objMail As Object

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)

With ActiveSheet
Set rngTo = .Range("j2")
Set rngSubject = .Range("A2")
Set rngBody = ActiveSheet.Range("A1:H36")
End With
rngBody.Copy

With objMail
.To = rngTo
.Subject = rngSubject
.Display 'Instead of .Display, you can use .Send to send the email _
or .Save to save a copy in the drafts folder
End With
SendKeys "^({v})", True

Set objOutlook = Nothing
Set objMail = Nothing

End Sub

[/pre]

 

[Sam Mathai Chacko]

Did you try hiding the form controls before copying the range?


And just out of curiosity, why are you using SendKeys "^({v})", True

 

[bvanscoy678]

No, I did not try hiding the controls, not sure how to do that. I merged a few pieces of code I found and "send keys" was just part of the merge. I really don't know what it means. Still pretty new to code, so learning as I go.

I updated part of the code to try and protect and unprotect my worksheet, but now it won't copy the range into the body of the email. Thanks!

[pre]

Sub CreateMail()

Dim rngSubject As Range
Dim rngTo As Range
Dim rngBody As Range
Dim objOutlook As Object
Dim objMail As Object

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
Dim ws As Worksheet

Call Unprotect

With ActiveSheet
Set rngTo = .Range("j13")
Set rngSubject = .Range("A1")
Set rngBody = ActiveSheet.Range("A1:H36")
End With
rngBody.Copy

With objMail
.To = rngTo
.Subject = rngSubject
.Display 'Instead of .Display, you can use .Send to send the email _
or .Save to save a copy in the drafts folder
End With
SendKeys "^({v})", True

Set objOutlook = Nothing
Set objMail = Nothing

Call Protect

End Sub

Sub Protect()
ActiveSheet.Protect "0921", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect "0921", Structure:=True, Windows:=False

End Sub

Sub Unprotect()
ActiveSheet.Unprotect "0921"
ActiveWorkbook.Unprotect "0921"
End Sub

[/pre]

 

[bvanscoy678]

I commented out my protection for now, so I could work on the form control. I attempted the below, but get an error message with me.control (I also tried activesheet.control)

[pre]

Sub CreateMail()

Dim rngSubject As Range
Dim rngTo As Range
Dim rngBody As Range
Dim objOutlook As Object
Dim objMail As Object

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
Dim ws As Worksheet
Dim ctl As Control

For Each ctl In Me.Controls
ctl.Visible = False
Next ctl

'Call Unprotect

With ActiveSheet
Set rngTo = .Range("j13")
Set rngSubject = .Range("A1")
Set rngBody = ActiveSheet.Range("A1:H36")
End With
rngBody.Copy

With objMail
.To = rngTo
.Subject = rngSubject
.Display 'Instead of .Display, you can use .Send to send the email _
or .Save to save a copy in the drafts folder
End With
SendKeys "^({v})", True

Set objOutlook = Nothing
Set objMail = Nothing

'Call Protect

For Each ctl In Me.Controls
ctl.Visible = True
Next ctl

End Sub

[/pre]