Dobut in sending mail thru VBA

[gopdeep]

pls have a luk on below code

Private Sub CommandButton1_Click()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim rng As Range
    Dim str As String, str1 As String
    Dim in1 As String, in2 As String
    in1 = InputBox("Range1:")
    in2 = InputBox("Range2:")
    Set rng = Sheets("sheet1").Range(in1 & ":" & in2).SpecialCells(xlCellTypeVisible)
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    strbody = ActiveSheet.Range(in1 & ":" & in2).Select
    str = "<font face='Trebuchet MS' size=2 color=blue >" & "Hi All," & "<br><br>" & "Good Morning !!!" & "<br><br>" & "Please find today's allocation below:" & "<br><br>" & "</font>"
    str1 = "<font face='Trebuchet MS' size=2 color=blue >" & "Thanks," & "<br>" & "GopDeep" & "</font>"
    On Error Resume Next
        With OutMail
        .To = "gopdeep@aaa.com"
        .CC = "gopdeep1@aaa.com"
        .BCC = ""
        .Subject = "Sample" & Chr(32) & Range("H4") & " Mail it is"
        .HTMLBody = str & RangetoHTML(rng) & "<br><br>" & str1
        .Display
        .ReadReceiptRequested = True
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

above code is working fine, but in mail body table is not appearing properly.

that is last row of the table is not having line in it... i couldn't resolve it even after many try... pls me

 

[Andrew Poulsom]

As I said I can reproroduce your problem if the last row doesn't have a bottom border. Under Format Cells|Borders tab it may appear to have a bottom border when actually it doesn't. What you are seeing is the top border of the cell below. So delete the bottom border and close the dialog. Then open the dialog and add a bottom border. Then try your code.

 

[gopdeep]

i've placed my document in below link, pls have a look and help me

http://www.4shared.com/file/XsirADzb/Mailer.html

 

[gopdeep]

Re: Dobut in sending mail thru VBA - Expert - pls help me

can any expert help me

 

[Andrew Poulsom]

Re: Dobut in sending mail thru VBA - Expert - pls help me

That site wants me to sign up. Try another one.

 

[gopdeep]

try the below one

http://www.sendspace.com/file/8qj5za

P.S: I've tried it from another system and was able to download without any credentials.

 

[Andrew Poulsom]

I get an error on this line:

Set rng = Sheets("Sheet1").Range(in1 & ":" & in2).SpecialCells(xlCellTypeVisible)

 

[gopdeep]

Ooops... but its working fine to me... problem is only with the table format in mail... at beginning of this loop you too tried with the same code and informed me that code was working fine to you right,.. but now i couldn't understand that why it is throwing error at below code as you saying

Set rng = Sheets("Sheet1").Range(in1 & ":" & in2).SpecialCells(xlCellTypeVisible)

but still i can suggest below way to over come it.

Just remove the below code from "Sheet1" part-

gopi:    in2 = InputBox("Range2:")
    If in2 = "" Then
    resp = MsgBox("No entry detected. Do you want to try again?", vbCritical + vbYesNo, "INVALID ENTRY")
    If resp = vbYes Then
    GoTo gopi
    Else
    Exit Sub
    End If
    End If

instead of this pls use like

in2="i7"

i've verified it and found that it is working fine

 

[Andrew Poulsom]

Sorry, I can't reproduce your problem. I get a bottom line in the EMail.