David123456
New Member
- Joined
- Jul 16, 2014
- Messages
- 48
Hi,
Please see VBA Code below I am struggling with code to end sub if a criteria is not met.
The line I am concerned with is
If Len(Range("d65")) = "Use Dropdown" Then Exit Sub
I want to be able to freeze screen if cell "d65" in each any sheet has the text use dropdown.
This is what I currently have this process E Mails an active sheet to a selected address - then freezes the sheet
Sub EmailWithOutlook()
Dim oApp As Object
Dim oMail As Object
Dim FilePath As String
Dim FileName As String
Application.ScreenUpdating = False
' create file name and path
FilePath = "Y:\" 'change the path as desired
FileName = FilePath & ActiveSheet.Name & ".pdf"
If ActiveSheet.Cells(65, "d").Value = "Use Dropdown" Then
MsgBox ("Please Complete!")
End If
If Len(Range("d65")) = "Use Dropdown" Then Exit Sub
'Now Export the Activesheet as PDF with the given File Name and path
Sheets(ActiveSheet.Name).ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
FileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
'Create and show the Outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = ActiveSheet.Cells(24, "D").Text
'Uncomment the line below to hard code a subject
.Subject = ActiveSheet.Cells(12, "H").Text
'Uncomment the lines below to hard code a body
.body = ActiveSheet.Cells(21, "D").Text
'Uncomment the lines below to hard code a body
.body = ActiveSheet.Cells(23, "D").Text
.Attachments.Add FileName
.Display
End With
'Delete the temporary file
'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub
Please see VBA Code below I am struggling with code to end sub if a criteria is not met.
The line I am concerned with is
If Len(Range("d65")) = "Use Dropdown" Then Exit Sub
I want to be able to freeze screen if cell "d65" in each any sheet has the text use dropdown.
This is what I currently have this process E Mails an active sheet to a selected address - then freezes the sheet
Sub EmailWithOutlook()
Dim oApp As Object
Dim oMail As Object
Dim FilePath As String
Dim FileName As String
Application.ScreenUpdating = False
' create file name and path
FilePath = "Y:\" 'change the path as desired
FileName = FilePath & ActiveSheet.Name & ".pdf"
If ActiveSheet.Cells(65, "d").Value = "Use Dropdown" Then
MsgBox ("Please Complete!")
End If
If Len(Range("d65")) = "Use Dropdown" Then Exit Sub
'Now Export the Activesheet as PDF with the given File Name and path
Sheets(ActiveSheet.Name).ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
FileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
'Create and show the Outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = ActiveSheet.Cells(24, "D").Text
'Uncomment the line below to hard code a subject
.Subject = ActiveSheet.Cells(12, "H").Text
'Uncomment the lines below to hard code a body
.body = ActiveSheet.Cells(21, "D").Text
'Uncomment the lines below to hard code a body
.body = ActiveSheet.Cells(23, "D").Text
.Attachments.Add FileName
.Display
End With
'Delete the temporary file
'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub