Automatic Email using details from Excel

  • Thread starter leslieneedshelp
  • Start date
L

leslieneedshelp

Hi,

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

Currently I'm managing our referral database and I'm wondering if ther
is a way to email the referring person on whether his referral passed o
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 include
the ff sheets: database, list used for the drop down, and the emai
messages to be sent out. For the email messages, is there a way t
include the name of the referral as part of the message?

Thank you again in advance for your help :)


Lesli

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

Vacuum Sealed

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.
 
P

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 purposecode. Feel free to ask or email me: (e-mail address removed)

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 thatit 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top