Drop down list disappears when spreadsheet is send via email

[dwarford101]

I have a number of drop down boxes (lists) in my spreadsheet, when i sent it as an attachment through outlook it seems the other side it dosent allow the drop down boxes to be shown.

Does any one know why and can this be prevented.

[contaminated]

Sounds like u send your e-maill as a message body. When u send as an excel file as body of a message , all formulas .., in short all features of excel are "turned off".
Try to send as an attachment

[dwarford101]

Sub Mail_workbook_Outlook_2()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim request As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object

Set wb1 = ActiveWorkbook

If Val(Application.Version) >= 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

TempFilePath = Environ$("temp") & "\"
request = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
FileExtStr = "." & LCase(Right(wb1.Name, _
Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

wb1.SaveCopyAs TempFilePath & request & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & request & FileExtStr)

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "LPM Request Form"
.Body = "LPM User information"
.Attachments.Add wb2.FullName

.Send
End With
On Error GoTo 0

wb2.Close SaveChanges:=False


Kill TempFilePath & request & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

As you can see its not saved under the body

[teylyn]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here