would like to include don't show gridline in send email vba

[pcorpz]

How can I disable gridline in the create temp file to send to outlook vba?

Sub Send_Email()
Dim Email_Subject, Email_Send_From, Email_Body As String, i As Integer
Dim Mail_Object, nameList As String, o As Variant, ws As Worksheet, ws1 As Worksheet
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set ws = Worksheets("SalesRep")
ans = MsgBox("Are you sure you want to send email's to all individuals in list ??", vbYesNo)
If ans = vbNo Then Exit Sub
For i = 2 To 10
Worksheets(ws.Range("A" & i).Value).Copy
With Worksheets(ws.Range("A" & i).Value).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteFormats
.Cells.PasteSpecial xlPasteValuesAndNumberFormats
End With
Application.CutCopyMode = False
TempFilePath = Environ$("temp") & ""
TempFileName = ws.Range("A" & i).Value & " " & Format(Now, "dd-mmm-yy")
FileExtStr = ".xlsx"
FileFormatNum = 51
With ActiveWorkbook
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
End With

 

[Leith Ross]

Hello pcorpz,

I have added indents to your code. As you can see what you posted is incomplete. Rather than turn Gridlines on and off and creating text files to attach to the email, why not add the Range into the body of the email?

Sub Send_Email()


    Dim Email_Subject, Email_Send_From, Email_Body As String, i As Integer
    Dim Mail_Object, nameList As String, o As Variant, ws As Worksheet, ws1 As Worksheet


        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        
        Set ws = Worksheets("SalesRep")
        
        ans = MsgBox("Are you sure you want to send email's to all individuals in list ??", vbYesNo)
        If ans = vbNo Then Exit Sub     ' Note: Because you have Disabled events, you need to Enab;le events before exiting
        
        For i = 2 To 10
            Worksheets(ws.Range("A" & i).Value).Copy
            
            With Worksheets(ws.Range("A" & i).Value).UsedRange
                .Cells.Copy
                .Cells.PasteSpecial xlPasteFormats
                .Cells.PasteSpecial xlPasteValuesAndNumberFormats
            End With
            
            Application.CutCopyMode = False
            
            TempFilePath = Environ$("temp") & ""
            TempFileName = ws.Range("A" & i).Value & " " & Format(Now, "dd-mmm-yy")
            
            FileExtStr = ".xlsx"
            FileFormatNum = 51
            
            With ActiveWorkbook
                .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
           ' Missing End With
        ' Missing Next i
        
End With

Here is a link that might help Mail Range/Selection in the body of mail

 

[pcorpz]

Hello pcorpz,

I have added indents to your code. As you can see what you posted is incomplete. Rather than turn Gridlines on and off and creating text files to attach to the email, why not add the Range into the body of the email?

Sub Send_Email()


    Dim Email_Subject, Email_Send_From, Email_Body As String, i As Integer
    Dim Mail_Object, nameList As String, o As Variant, ws As Worksheet, ws1 As Worksheet


        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        
        Set ws = Worksheets("SalesRep")
        
        ans = MsgBox("Are you sure you want to send email's to all individuals in list ??", vbYesNo)
        If ans = vbNo Then Exit Sub     ' Note: Because you have Disabled events, you need to Enab;le events before exiting
        
        For i = 2 To 10
            Worksheets(ws.Range("A" & i).Value).Copy
            
            With Worksheets(ws.Range("A" & i).Value).UsedRange
                .Cells.Copy
                .Cells.PasteSpecial xlPasteFormats
                .Cells.PasteSpecial xlPasteValuesAndNumberFormats
            End With
            
            Application.CutCopyMode = False
            
            TempFilePath = Environ$("temp") & ""
            TempFileName = ws.Range("A" & i).Value & " " & Format(Now, "dd-mmm-yy")
            
            FileExtStr = ".xlsx"
            FileFormatNum = 51
            
            With ActiveWorkbook
                .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
           ' Missing End With
        ' Missing Next i
        
End With

Here is a link that might help Mail Range/Selection in the body of mail

Hi Leith! Thanks for your reply. Sorry, I didn't include the rest of the code with the assumption that it's not needed for this particular post (to add the don't show gridline code). Also to answer your question, I prefer to have it copied as an attachment than the body of email. Easier for the users to navigate and update. =)

 

[Leith Ross]

Hello pcorpz,

You can turn off Gridlines like this...

ActiveWindow.DisplayGridlines = False

To restore them...

ActiveWindow.DisplayGridlines = True

 

[pcorpz]

Hello pcorpz,

You can turn off Gridlines like this...

ActiveWindow.DisplayGridlines = False

To restore them...

ActiveWindow.DisplayGridlines = True

Thanks Leith.. I realize this is the code to turn it off but my challenge is incorporating it to the send email vba? Where would I add this line?..

 

[Leith Ross]

Hello pcorpz,

Turn them off at the start of the code and restore them before you exit the routine. Here is an example...

Sub Send_Email()


    Dim Email_Subject, Email_Send_From, Email_Body As String, i As Integer
    Dim Mail_Object, nameList As String, o As Variant, ws As Worksheet, ws1 As Worksheet


        ActiveWindow.DisplayGridlines = False
        
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        
        Set ws = Worksheets("SalesRep")
        
        ans = MsgBox("Are you sure you want to send email's to all individuals in list ??", vbYesNo)
        If ans = vbNo Then Exit Sub     ' Note: Because you have Disabled events, you need to Enab;le events before exiting
        
        For i = 2 To 10
            Worksheets(ws.Range("A" & i).Value).Copy
            
            With Worksheets(ws.Range("A" & i).Value).UsedRange
                .Cells.Copy
                .Cells.PasteSpecial xlPasteFormats
                .Cells.PasteSpecial xlPasteValuesAndNumberFormats
            End With
            
            Application.CutCopyMode = False
            
            TempFilePath = Environ$("temp") & ""
            TempFileName = ws.Range("A" & i).Value & " " & Format(Now, "dd-mmm-yy")
            
            FileExtStr = ".xlsx"
            FileFormatNum = 51
            
            With ActiveWorkbook
                .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
           ' Missing End With
        ' Missing Next i
        
        ActiveWindow.DisplayGridlines = True
        
End With

 

[pcorpz]

Hello pcorpz,

Turn them off at the start of the code and restore them before you exit the routine. Here is an example...

Sub Send_Email()


    Dim Email_Subject, Email_Send_From, Email_Body As String, i As Integer
    Dim Mail_Object, nameList As String, o As Variant, ws As Worksheet, ws1 As Worksheet


        ActiveWindow.DisplayGridlines = False
        
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        
        Set ws = Worksheets("SalesRep")
        
        ans = MsgBox("Are you sure you want to send email's to all individuals in list ??", vbYesNo)
        If ans = vbNo Then Exit Sub     ' Note: Because you have Disabled events, you need to Enab;le events before exiting
        
        For i = 2 To 10
            Worksheets(ws.Range("A" & i).Value).Copy
            
            With Worksheets(ws.Range("A" & i).Value).UsedRange
                .Cells.Copy
                .Cells.PasteSpecial xlPasteFormats
                .Cells.PasteSpecial xlPasteValuesAndNumberFormats
            End With
            
            Application.CutCopyMode = False
            
            TempFilePath = Environ$("temp") & ""
            TempFileName = ws.Range("A" & i).Value & " " & Format(Now, "dd-mmm-yy")
            
            FileExtStr = ".xlsx"
            FileFormatNum = 51
            
            With ActiveWorkbook
                .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
           ' Missing End With
        ' Missing Next i
        
        ActiveWindow.DisplayGridlines = True
        
End With

Thank you Leith! This works. =D I was unsure where to include it and the couple attempts I inserted it, I got the run-time error. Still trying to understand vba and its concepts. Thanks so much!

 

[Leith Ross]

Hello pcorpz,

You're welcome. Glad you got it working.