[leslieneedshelp]

Hi,

I'm new here and the forums have been quite useful. However I am getting confused since i'm not an expert with Excel.

Currently I'm managing our referral database and I'm wondering if there is a way to email the referring person on whether his referral passed or failed initial screening, or is an invalid referral.

I'd appreciate very much if someone could help me out.

I'm attaching sample of how the database. Also, the excel file includes the ff sheets: database, list used for the drop down, and the email messages to be sent out. For the email messages, is there a way to include the name of the referral as part of the message?

Thank you again in advance for your help :)


Leslie

[Vacuum Sealed]

On 5/07/2012 7:21 PM, leslieneedshelp wrote:
Hi,

I'm new here and the forums have been quite useful. However I am getting
confused since i'm not an expert with Excel.

Currently I'm managing our referral database and I'm wondering if there
is a way to email the referring person on whether his referral passed or
failed initial screening, or is an invalid referral.

I'd appreciate very much if someone could help me out.

I'm attaching sample of how the database. Also, the excel file includes
the ff sheets: database, list used for the drop down, and the email
messages to be sent out. For the email messages, is there a way to
include the name of the referral as part of the message?

Thank you again in advance for your help :)


Leslie


+-------------------------------------------------------------------+
|Filename: ERPsample.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=453|
+-------------------------------------------------------------------+



Hi

Check out Ron DeBruin's site

http://www.rondebruin.nl/sendmail.htm

HTH
Mick.

[pascal baro]

Hi,
If your query is still alive, look for the code below. If you find this group useful, then you should have the skills to code further the code below since it's a lot of text and string manipulation...It should work with any email client. I don't have Outlook that's why I'm using this general purpose code. Feel free to ask or email me:

Sub SendEmail()

Call ASet

Dim URL As String
Dim strEm As String
Dim strSubj As String
Dim strURL As String

Dim i As Long, j As Long
Dim lastrERP As Long, lastrEM
Dim StatERP As String

lastrERP = wsERP.Cells(Rows.Count, 5).End(xlUp).Row
lastrEM = wsEm.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrERP

StatERP = wsERP.Cells(i, 8).Value
MTmp = wsERP.Cells(i, 6).Value

For j = 2 To lastrEM

If StatERP = wsEm.Cells(i, 1).Value Then

strURL = CStr(wsERP.Cells(i, 5).Value)
strEm = MPassedA & " " & MTmp & MPassedB

strSubj = "Referral"

End If

Next j
Next i

strURL = "mailto: " & strURL & "?subject=" & strSubj & "&body=" & strEm
ShellExecute 0&, vbNullString, strURL, vbNullString, vbNullString, vbNormalFocus

Application.SendKeys "%s"

Call ZSet

End Sub

'''
Public Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

'''GLOBAL VARIABLES
Public wsERP As Worksheet, wsEm As Worksheet

Public MPassedA As String
Public MPassedB As String
Public MFailed As String
Public MContact As String
Public MInvRefa As String
Public MInvRefb As String
Public MTmp As String


'''SUB CLASS LIKE
Sub ASet()

Set wsERP = ThisWorkbook.Worksheets("ERP")
Set wsEm = ThisWorkbook.Worksheets("email messages")

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With

MPassedA = "": MPassedB = ""
MPassedA = MPassedA & "Hi," & vbCrLf & vbCrLf
MPassedA = MPassedA & "Thank you for referring "

MPassedB = vbCrLf & vbCrLf & MPassedB & "We would like to advise you that it is a valid referral and " & MTmp & " passed the initial screening. " & vbCrLf & vbCrLf
MPassedB = MPassedB & "Thank You," & vbCrLf & "Star Team"

MPassedA = Application.WorksheetFunction.Substitute(MPassedA, vbCrLf, "%0D%0A")
MPassedB = Application.WorksheetFunction.Substitute(MPassedB, vbCrLf, "%0D%0A")

End Sub

Sub ZSet()

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
End With

Set wsERP = Nothing
Set wsEm = Nothing

End Sub


Pascal Baro