Send email with data depending on active cell...

[sibsanne]

Dears,

I'm new to excel.
A month ago had to start to work with it and a lot of action I need to perform are quit recurrent (my wrist hurts :().
So I try to automate them as much as possible.
I have been able to do a lot already.

I need a last macro that I action trough a button.
I use excel 2007 and outlook 2007.

I'll try to explain what I need:
Depending on the row I'm on by triggering a button I need a ready to send email with attachment to be generated.
The mail should be pre-filled with the data that can be found in the row.
Here is the template.


To: VALUE IN #Y
Cc: VALUE IN #Z

Subject: TEXT TEXT VALUE IN #A/VALUE IN #B VALUE IN #C VALUE IN #D
Attached: VALUE IN #W

'TEXT,'

'TEXT TEXT TEXT TEXT TEXT'
TEXT: VALUE IN #A
TEXT: VALUE IN #B
TEXT: VALUE IN #C
TEXT: VALUE IN #D
TEXT: VALUE IN #M

'TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT.'
'TEXT.'

'My outlook signature'


# would be the value of the row i'm on
TEXT stands for some free text I need to complete the mail template.
The attached file is an external excel file or pdf file.
I could also gather all the data needed for the subject in one cell under one column.
The value in the attach form is the URL to the file.
Do you think one macro triggered trough a button can do that and gather the info depending on the row your are on?: pray2:

Thank a lot for your time.
I know it's a elaborated request.
I've found a lot on the net, but nothing that goes close to what I need or that I'm able to adapt myself.

Best regards,

[mdmackillop]

I use this to send email to various recipients separately. Maybe it can be adapted to your needs. It requires Redemption installed, to get round Outlook security

[sibsanne]

Thx mdmackillop,

After 3 days of seeking :banghead:, I miraculously got a vba that does all what I need !!!.

I mixed two codes that I found on the net, and at some point it worked.

Your code looks like it at some parts.
I don't know anymore if I used it or not.
Because I have been looking at a lot of codes and so bit by bit understood the syntax of it.

Sometime I blocked on thing like = instead of & etc.

So "if" :yes, thanks for the inspiration.. and "if not" Thx anyway for trying to help.

:thumb

[mdmackillop]

Can you post your solution for the benefit of others?

[sibsanne]

Sure! Here you go.


Function GetBoiler(ByVal sFile As String) As String
'Dick Kusleika
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function

Sub SendBlanco()
' Don't forget to copy the function GetBoiler in the module.
' Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim msg As String
Dim SigString As String
Dim Signature As String, Email As String, Cc As String, Bcc As String, Subj As String, Att As String

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

' Compose the message
ThisRow = ActiveCell.Row
msg = "Beste," & "<br><br>" & "text text text" & "<br>"
msg = msg & "<b>NET:</b>" & " " & Cells(ThisRow, 1) & "<br>"
msg = msg & "<b>JMS:</b>" & " " & Cells(ThisRow, 2) & "<br>"
msg = msg & "<b>SCOPE:</b>" & " " & Cells(ThisRow, 3) & "<br>"
msg = msg & "<b>PO:</b>" & " " & Cells(ThisRow, 4) & "<br>"
msg = msg & "<b>DESCRIPTION:</b>" & " " & Cells(ThisRow, 13) & "<br>"
msg = msg & "<br>" & "text text text text." & " <br>" & "text."
Email = Cells(ThisRow, 21)
Cc = Cells(ThisRow, 22)
Bcc = Cells(ThisRow, 23)
Subj = "text" & " " & Cells(ThisRow, 1) & "/" & Cells(ThisRow, 2) & " " & Cells(ThisRow, 4) & " " & Cells(ThisRow, 3)
Att = Cells(1, 12) & Cells(ThisRow, 19)


'Use the second SigString if you use Vista or win 7 as operating system

SigString = "C:\..."

'SigString = "C:\Users\" & Environ("user") & _
"\AppData\Roaming\Microsoft\Signatures\name.htm"

If Dir(SigString) <> "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If

On Error Resume Next
With OutMail
.To = Email
.Cc = Cc
.Bcc = Bcc
.Subject = Subj
.HTMLBody = msg & "<br><br>" & Signature
'You can add files also like this
.Attachments.Add Att
.Display 'or use .Send
End With

On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub



:cool: