• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to email active excel sheet as attachment but name of sheet should same as given for excel sheet

S

Shabbo

Member
How to email active excel sheet as attachment but name of sheet should same as given for excel sheet.

Eg : If I have 10 Excel worksheets in my workbook and I wanted to send sheet “ABC” which is active now I wanted to attached sheet “ABC” in my outlook but attachment name should show as “ABC” only.
 
Dear Sir,

Ref to attached sheet, I wanted to send an auto email to my customers and I have attached my sample excel file for your reference because original file size is big.

As per VBA code my A2 column will be the email address to whom I wanted to send an email as TO.

As per VBA code my A3 column will be the email address to whom I wanted to send an email as CC.

As per VBA code my A4 column will be the subject Line.

I wanted to generate report like once I double click into pivot a new sheet will generate for that customer but A2 and A3 should be the email address from customer contact details sheet.
 

Attachments

  • OS reprot help.xlsx
    19.3 KB · Views: 6
Luke M said:
Check out the code examples here:
http://www.rondebruin.nl/win/s1/outlook/mail.htm

One of these should be exactly what you're looking for.
Click to expand...

Dear Sir,

I have copied this VBA code and its working as well thanks for the same.

Just one change is needed that is subject line.

Now the subject line is “This is the Subject line” which needs to be changed as follows.

Subject Line Should be name of attachment. Eg my file name is Reliable outstanding as on 11th Oct 2016 the subject line should be the “Reliable outstanding as on 11th Oct 2016”.
 
If thats the whole file name, you can just reference that in the code. I'm assuming you already had to make a variable which has the file name.


Otherwise, can you post your current code?
 
Luke M said:
If thats the whole file name, you can just reference that in the code. I'm assuming you already had to make a variable which has the file name.


Otherwise, can you post your current code?
Click to expand...

Dear Sir,

Please note the code I wanted to change subject line as file name.

Code: 
Sub Mail_Every_Worksheet()
'Working in Excel 2000-2016
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object

    TempFilePath = Environ$("temp") & "\"

    If Val(Application.Version) < 12 Then
        'You use Excel 97-2003
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        'You use Excel 2007-2016
        FileExtStr = ".xlsm": FileFormatNum = 52
    End If

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

    Set OutApp = CreateObject("Outlook.Application")

    For Each sh In ThisWorkbook.Worksheets
        If sh.Range("A1").Value Like "?*@?*.?*" Then

            sh.Copy
            Set wb = ActiveWorkbook

            TempFileName = "Sheet " & sh.Name & " of " _
                        & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

            Set OutMail = OutApp.CreateItem(0)

            With wb
                .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum

                On Error Resume Next
                With OutMail
                    .to = sh.Range("A1").Value
                    .CC = ""
                    .BCC = ""
                    .Subject = "This is the Subject line"
                    .Body = "Hi there"
                    .Attachments.Add wb.FullName
                    'You can add other files also like this
                    '.Attachments.Add ("C:\test.txt")
                    .Send  'or use .Display
                End With
                On Error GoTo 0

                .Close savechanges:=False
            End With
           
            Set OutMail = Nothing

            Kill TempFilePath & TempFileName & FileExtStr

        End If
    Next sh

    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub
 
Luke M said:
Perfect, thank you. Change the line that sets the subject to this:
Code: 
.Subject = wb.Name
Click to expand...
Dear Sir,

Now I can see the changes in subject line but still I cant see my HTML signature in email my email goes without signature what is solution ?


Code: 
Sub Mail_Every_Worksheet()
'Working in Excel 2000-2016
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
  Dim sh As Worksheet
    Dim wb As Workbook
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    strbody = "Dear Sir," & vbNewLine & vbNewLine & _
              " Ref to above subjec Please find attached herewith outstanding Statment for "

    TempFilePath = Environ$("temp") & "\"

    If Val(Application.Version) < 12 Then
        'You use Excel 97-2003
      FileExtStr = ".xls": FileFormatNum = -4143
    Else
        'You use Excel 2007-2016
      FileExtStr = ".xlsm": FileFormatNum = 52
    End If

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

    Set OutApp = CreateObject("Outlook.Application")

    For Each sh In ThisWorkbook.Worksheets
        If sh.Range("A1").Value Like "?*@?*.?*" Then

            sh.Copy
            Set wb = ActiveWorkbook

            TempFileName = "Sheet " & sh.Name & " of " _
                        & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

            Set OutMail = OutApp.CreateItem(0)

            With wb
                .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum

                On Error Resume Next
                With OutMail
                        .to = sh.Range("A1").Value
                        .CC = ""
                        .BCC = ""
                        .Subject = ActiveWorkbook.Name
                        .Body = strbody
                   
                   
                    .Attachments.Add wb.FullName
                    'You can add other files also like this
                  '.Attachments.Add ("C:\test.txt")
                  .Send  'or use .Display
              End With
                On Error GoTo 0

                .Close savechanges:=False
            End With
         
            Set OutMail = Nothing

            Kill TempFilePath & TempFileName & FileExtStr

        End If
    Next sh

    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub
 
First thing I've read about the signature line. When you build the email like this, nothing will appear by default. I.e., you need to supply in the code EVERYTHING (including your signature) that you want to appear in the email. Perhaps you can just change this line
Code: 
strbody = "Dear Sir," & vbNewLine & vbNewLine & _
              " Ref to above subjec Please find attached herewith outstanding Statment for "
to something like this?
Code: 
strbody = "Dear Sir," & vbNewLine & vbNewLine & _
            " Ref to above subjec Please find attached herewith outstanding Statment." & _
           vbnewline & vbnewline & "Sincerely," & vbnewline & "Shabbo"
 
You must log in or register to reply here.
Back
Top