Macro help for Excel to automatically fill in certain cell info into outlook email

[isekii]

I was wondering if anyone can help me with a similar issue. I tried using the code above as a template and modified some stuff but was unable to get it working.

What the macro needs to do is. When it's run, I would like it to automatically fill in the person's name; client name and submission date in an email in Outlook


Subj: Commission Request

Body:
“Thank you :Submitter Name: for submitting your commission request for :Client Name: It has been received by Julie Goldberg on :Submission Date: and is awaiting approval.

Regards,”


Can anyone assist ?

template excel file
https://dl.dropboxusercontent.com/u/24662260/CommissionTemplate.xlsx

Excel 2007//2010
Outlook 2007//2010

 

[postmark]

Maybe this would be easier by doing it from Outlook via mail merge?

http://office.microsoft.com/en-us/w...-to-your-e-mail-address-list-HA010109162.aspx

Oh and there was no code in your post

 

[isekii]

Maybe this would be easier by doing it from Outlook via mail merge?

http://office.microsoft.com/en-us/w...-to-your-e-mail-address-list-HA010109162.aspx

Oh and there was no code in your post

Ah~ i copied and pasted from another forum.
What it is is that I'm going to have an excel sheet with names.

and when I run the macro just personalize each email per line with the name.

 

[postmark]

Yeah I understand what you're trying to do. I'll work something up for you

 

[isekii]

Yeah I understand what you're trying to do. I'll work something up for you

really appreciate it.

This was what I was trying to work off of from another forum but had no luck

Sub SendEmail()
'Macro Purpose: To send an email through Outlook
Dim objOL As Object
Dim objMail As Object
Dim sEmail As String
Dim sEmailColumn As String
Dim sSubject As String
Dim sBody As String
Dim lDataRow As Long
Dim cl As Range
'Set column with email address
sEmailColumn = "Q"
For Each cl In Selection.Resize(, 1)
'Generate required info
lDataRow = cl.Row
'Check if remediation required
If cl.Parent.Range("L" & lDataRow).Value = "Urgent" Then
With cl.Parent
sEmail = .Range(sEmailColumn & lDataRow)
sSubject = "Agreement " & .Range("B" & lDataRow) & " requires urgent remediation!"
sBody = "Remediation Required:" & vbNewLine & .Range("H" & lDataRow) & _
vbNewLine & vbNewLine & "Advisors Comments:" & vbNewLine & .Range("N" & lDataRow) & _
vbNewLine & vbNewLine & "Management Comments:" & vbNewLine & .Range("O" & lDataRow)
End With
'Turn on error handling
On Error GoTo Cleanup
'Bind to Outlook
Set objOL = CreateObject("Outlook.Application")
'Create a new email and send it
Set objMail = objOL.CreateItem(0) '0=olmailitem
With objMail
.To = sEmail
.Subject = sSubject
.Body = sBody
.Display
End With
End If
Next cl
Cleanup:
'Release all objects
Set objMail = Nothing
Set objOL = Nothing
On Error GoTo 0
End Sub

 

[postmark]

So that script works for me, but not with the base data you have in your excel sheet. I'm assuming you have the other required fields in your copy of the excel sheet? This script requires you to make a selection (highlight A1-A3 for example) then it will check column Q for each of those rows and see if it says "Urgent". if that is satisfied then it will create an email grabing the address and other things from each of the specified rows. I have modified it to not require the "Urgent" in column Q and also it will pull the email address out of column H now.

Sub Macro1()
'
' Macro1 Macro
'

'

'Macro Purpose: To send an email through Outlook
Dim objOL As Object
Dim objMail As Object
Dim sEmail As String
Dim sEmailColumn As String
Dim sSubject As String
Dim sBody As String
Dim lDataRow As Long
Dim cl As Range
'Set column with email address
sEmailColumn = "H"
For Each cl In Selection.Resize(, 1)
'Generate required info
lDataRow = cl.Row
'Check if remediation required
'If cl.Parent.Range("L" & lDataRow).Value = "Urgent" Then
With cl.Parent
sEmail = .Range(sEmailColumn & lDataRow)
sSubject = "Commission Request"

sBody = "Thank you " & .Range("A" & lDataRow) & " for submitting your commission request for " & .Range("B" & lDataRow) & _
" It has been received by Julie Goldberg on " & .Range("C" & lDataRow) & " and is awaiting approval." & _
vbNewLine & vbNewLine & "Regards , ”"
End With
'Turn on error handling
On Error GoTo Cleanup
'Bind to Outlook
Set objOL = CreateObject("Outlook.Application")
'Create a new email and send it
Set objMail = objOL.CreateItem(0) '0=olmailitem
With objMail
.To = sEmail
.Subject = sSubject
.Body = sBody
.Display
End With
'End If
Next cl
Cleanup:
'Release all objects
Set objMail = Nothing
Set objOL = Nothing
On Error GoTo 0

End Sub

 

[isekii]

<3 Tested it and it works great. Thank you!!

The one I listed above was a sample for another one that someone else requested. So I tried to modify that to my needs but had no luck.

 

[postmark]

Glad I could help out. Just an FYI, this one here will just open each email (.Display line in the code). This can be changed to automatically send the email as well if you change that to .Send