Help With VBA Code - Range not working

[Nordicrx8]

Hey all,

I've been using the below code for some time now, and it works great, but I'm looking to automate it. The code sends an email of a range to me directly. However, I'm looking for the code to now reference a cell for an email address, rather than me typing in my address manually. (I want it to automatically CC members based on cell data) Problem being, when I use the range function, it's not working.

I put the problem areas in BOLD. thought's on why this isn't working as intended?

Also, while I'm here... for some reason when it sends the email, it strips it of some of it's formatting. (Column width is incorrect, grid lines show) Any Idea how to instruct it to keep all formatting?

Thanks in advance for any help!

Chris
---------------

Sub Mail_Range()

If MsgBox("Submissions are FINAL. Are you sure you want to submit?", vbYesNo) = vbNo Then Exit Sub

    Dim Source As Range
    Dim Dest As Workbook
    Dim wb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim OutApp As Object
    Dim OutMail As Object

    Set Source = Nothing
    On Error Resume Next
    Set Source = Range("G1:N49").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If Source Is Nothing Then
        MsgBox "The source is not a range or the sheet is protected, please correct and try again.", vbOKOnly
        Exit Sub
    End If

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

    Set wb = ActiveWorkbook
    Set Dest = Workbooks.Add(xlWBATWorksheet)

    Source.Copy
    With Dest.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial Paste:=xlPasteValues
        .Cells(1).PasteSpecial Paste:=xlPasteFormats
        .Cells(1).Select
        Application.CutCopyMode = False
    End With

    TempFilePath = Environ$("temp") & "\"
    TempFileName = Range("J3,J11")

    If Val(Application.Version) < 12 Then
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        FileExtStr = ".xlsx": FileFormatNum = 51
    End If

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

    With Dest
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
        With OutMail
            .to = Range("T3")
            .CC = ""
            .BCC = ""
            .Subject = Range("J11")
            .Body = " "
            .Attachments.Add Dest.FullName
            .Send
        End With
        On Error GoTo 0
        .Close savechanges:=False
    End With

    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    MsgBox ("Form submitted. Please check your sent mail for confirmation and keep for your records.")
End Sub

 

[daverunt]

Hi,

Try adding .Value to the range.
If that doesn't work try qualifying the sheet name.

Are you using the content of the 2 cells for the filename?
I added a space in between if so.

TempFileName = Range("J3").Value & " " &  Range("J11").Value

.to = Range("T3").Value
.Subject = Range("J11").Value

I don't see a range in the mail body?
You can use the RangetoHTML function here with .HTMLBody to keep a range format in a mail.

History | Ron de Bruin

Welcome to https://www.rondebruin.nl

www.rondebruin.nl

 

[Nordicrx8]

Hey there,

I was hoping the .Value would be a simple fix, but it's still not working. I'm having some difficulty adding the sheet to qualifications - could you lend a hand? Am still a novice at VBA - I know just enough to get myself into trouble.

thanks for your help!

 

[Nordicrx8]

Nevermind - I figured it out! thanks again for your help!

 

[daverunt]

Glad to help - but I don't know if I did?

Nobody else will know what the fix was unless you post the code....

 

[Nordicrx8]

Glad to help - but I don't know if I did?

Nobody else will know what the fix was unless you post the code....

Fair point! I actually didn't change any of the code. I had the code in a module previously. I deleted the module and posted the code into the worksheet itself, and the range for the email address worked! Not sure for the reasoning behind it, but I'm not complaining.