Excel VBA to Attach File And Automatically Send Email

**br.giri68** · 09-13-2012, 01:47 PM

**Friel300** · 09-13-2012, 02:26 PM

E-Mailing from excel

hey this is a piece from my refereence file
it should work pretty well.

it should all be pretty straight forward to taylor to your needs.

Code:

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

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

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "Cell A1 is changed" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    On Error Resume Next
    With OutMail
        .To = Email@Domain.com		'Email Address (can be a cell reference)
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody 
        '.Attachments.Add ("C:\test.txt") 	'You can add a file like this
        .Display  					 'or use .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

**Friel300** · 09-13-2012, 02:32 PM

oh sorry and the question would go like

Code:

if msgbox("Do you want to send an e-mail?",vbQuestion+vbYesNo,"Send Email" ) = vbNo then exit sub

at the start of the code.

**malay** · 07-03-2013, 01:09 PM

Originally Posted by Friel300

E-Mailing from excel

hey this is a piece from my refereence file
it should work pretty well.

it should all be pretty straight forward to taylor to your needs.

Code:

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

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

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "Cell A1 is changed" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    On Error Resume Next
    With OutMail
        .To = Email@Domain.com		'Email Address (can be a cell reference)
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody 
        '.Attachments.Add ("C:\test.txt") 	'You can add a file like this
        .Display  					 'or use .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

any problems give me a shout =D

This is quite good but what i need is to forward all mails saved in a folder to a different recipient with the attachments on the mails.I find it difficult to do and the above solution did not serve the purpose as attachments are sent from the hard drive location and not from the saved mails. Please help me to forward all mails in a saved folder with attachments if any.

**Friel300** · 07-03-2013, 01:44 PM

you could add the mail that you want to forward to the attachments.

**malay** · 07-04-2013, 11:08 AM

Hi,

Thank you for the reply...but let me expand the question for your better understanding.

I have copied some mails from outlook inbox to a folder. Some mails are with attachments and some are without. I need to automate it with excel vba to forward those mails to an email ID that i provide in one cell of excel sheet. I need to forward the mails to keep the format and attachments unchanged. Please help me to solve the problem as it's a project I am doing and having problem solving it on time.

Regards,
Malay

**Excel Fox** · 07-04-2013, 11:41 AM

So are you saying that you want to open the saved mails from the hard disk folder, and then just forward those to certain mail IDs?

**Friel300** · 07-04-2013, 12:07 PM

If you want to email all files in a folder, to an email address
(in this case it will be the address in cell A2)

then this will work

Code:

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim StrFile As String

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

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "See attached" & vbNewLine


    On Error Resume Next
    With OutMail
        .To = Range("a2").Value      'Email Address (can be a cell reference)
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody
        StrFile = Dir("C:\*")  'location of files to send
        Do While Len(StrFile) > 0
            .Attachments.Add ("C:\" & StrFile) 'insert your file path here too no Asterisk *
            StrFile = Dir
        Loop
        .Display                     'or use .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

if you want to open the mails and send them i dont think i will be able to help

**malay** · 07-05-2013, 10:20 AM

Hi,

Thank you so much, but sorry to say this solution did not solve my purpose. I need to open each saved ".msg" file and forward it to a certain mail address, reason i want to forward it to keep the mail format and attachment unchanged.

I can read the mail and then take the body content to another mail and then send it to the recipients with attachments if any but after doing this the format get messed. So can you please help me to format the body of the mail content after i put the body content from the saved mails to the new mail body. I am following your code to send mails. So use the same method but provide me solution to format the body of the mail.

Thanks...

**Friel300** · 07-05-2013, 12:12 PM

Morning Malay

unfortunately I am not all that informed on this subject, as I only ever needed it to generate new mails. I have never worked with reading them or forwarding them.

For the record I have just tried to send an email, that has attachments & was saved in a folder, as an attachment. it worked fine with the attachments and formatting.