Excel Template With Macro For Sending Mails And Attachment Through Outlook

[Excel Fox]

I haven't tested this. But I think it should work. You can enter the lines and provide line spaces in the cell itself using the ALT+ENTER keys. That way, you will get the exact text as it is in the cell.

[Murali K]

Thanks for your replay,
I have tested 'ALT+Enter' method also and 'wrap-text method, both doesn't worked.................'
can it possible to add body of the message in the code itself.............................
Thanks in advance......

[Excel Fox]

Yes you can. So in place of

strMessage:=.Cells(lngLoop, 8).Value

, use

strMessage:="Your message here" & vblf & "Here's the next line"

[burzum]

Thanks for this VB script. It is very close to what I'm looking to do.

My question is: How can I apply this script on a row by row basis?

I would like to be able to send a single or a few emails at a time. For example, if I want to send the email with the attachment and the values are stored in row 6 of the current sheet, is there a way to set the macro to prompt me to enter the row(s) I want it to process for recipient, subject attachment, body, etc?

[Excel Fox]

Can be done. But before suggesting anything, would like to know why do you keep rows for the ones which you don't want to send mail to?

[burzum]

I'm keeping a list of cases that are in progress and what I need to do before my consultation is complete. When a consultation report is finished, I'd like to hit the send mail button to notify the person who submitted the case for my consultation and notify only that person and have my report sent as a file attachment to the email.

[bakerman]

A very basic example on how to automaticaly send email one row at a time.
By putting ok in column I the email is constructed with all of the data in the same row.
At the same time you have a check to which person(s) you already sent an email.

[burzum]

Thank you bakerman, this script is exactly what I'm looking for.

I'm having a problem though in that if I copy a range of cells from another sheet to a blank row, the script triggers regardless of whether or not "OK" is in the targeted cell in the column. How can I modify the script to ONLY trigger when "OK" is typed into the targeted cells?

[bakerman]

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column = 9 Then
        If UCase(Target.Value) = "OK" Then
            With CreateObject("Outlook.Application").CreateItem(0)
                .To = Cells(Target.Row, 8).Value
                .Subject = Cells(Target.Row, 4).Value
                .Body = "Dear " & Cells(Target.Row, 3).Value & " " & Cells(Target.Row, 2) & "," & vbLf & vbLf & _
                    "The attachment provided is my final consultation report." & vbLf & vbLf & _
                    "Sincerely yours" & vbLf & vbLf & _
                    "Burzum"
                .Attachments.Add Cells(Target.Row, 7).Value
                .Display '.Send
            End With
        End If
    End If
    
End Sub

[bandus]

Dear Excel Fox,

I started using this macro and made some modification but nothing significant. However unfortunately I cannot use it for sending for multiple e-mail addresses. I type the emails as the outlook requires (abc@def; ghi@jkl; etc) but it doesn't work. I checked and the addresses are copied into the right field in the right way, but the outlook doesn't recognize them as an e-mail address. This only occurs if I type more than one emails into the excel cell. If I click into the To or CC field in outlook and then click somewhere else or press the tab, the emails get recognized (and underlined) and the message could be sent.

Do you have any idea how could I skip this step which really slows down the distribution of e-mails?

Thanks in advance!