Thursday, January 12, 2012

User defined function in excel to sent an email from Outlook



Function Email_Send (too As Variant, Optional ccc As String, Optional attach As String, Optional subj As String)

Dim otl As New Outlook.Application  ''''''Tools->Reference-Microsoft Outlook 12.0 Object Library'''''
Dim ml As MailItem
Set ml = otl.CreateItem(olMailItem)
If Len(too) = 0 Then
Email_Send = "Not Sent -> Blank Email Id"
Exit Function
End If
For i = 1 To Len(too)
If Mid(too, i, 1) = "@" And Right(too, 4) = ".com" Or Right(too, 4) = ".net" Then
Exit For
End If
Next
If i > Len(too) Then
Email_Send = "Not Sent -> Invalid Email"
Else
With ml
           ml.To = too
           If Len(ccc) > 0 Then
           ml.CC = ccc
           End If
           If Len(attach) > 0 Then
           ml.Attachments.Add attach
           End If
           If Len(subj) > 0 Then
           ml.Subject = subj
           End If
           ml.Display
End With
Email_Send = "Email Sent"
End If

End Function

No comments:

Post a Comment

Excel VBA to send email from excel with HTML table in a email body

'' Excel vba script to send email from outlook & email body in HTML table format. ''Write down email body in HTML tags...