ClioSport.net

Register a free account today to become a member!
Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!

  • When you purchase through links on our site, we may earn an affiliate commission. Read more here.

One for the VB people..... sending emails.



mace¬

ClioSport Club Member
  Clio
Afternoon, was just wondering if anyone can help me with a quck VB question I have.

I have written/stolen/hijacked some code that creates opens an email in Outlook, populates a number of emails with some data from access. email addresses and other data blah blah blah.

Problem is the emails are being sent to the customers from my email address and coming back to me.

Question: What code do I need to add a 'from' email address and 'replies to' email address?

Ta.
Pete
 

mace¬

ClioSport Club Member
  Clio
Code:
Public Function zzPJM_SendEMail()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim MyNewBodyText As String

Set fso = New FileSystemObject

 ' First, we need to know the subject.
 ' We can't very well be sending around blank messages...

Subjectline$ = "HP Support Incident customer Feedback"

 ' If there's no subject, call it a day.

If Subjectline$ = "" Then
    MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
        "Quitting...", vbCritical, "E-Mail Merger"
    Exit Function
End If
    
 ' Now we need to put something in our letter...

'BodyFile$ = InputBox$("Please enter the filename of the body of the message.", _
'             "We Need A Body!")

'Use a pre formatted text file.....
BodyFile$ = "C:\Documents and Settings\masonpet\My Documents\FeedbackTest.html"

 ' If there's nothing to say, call it a day.

If BodyFile$ = "" Then
    MsgBox "No body, no message." & vbNewLine & vbNewLine & _
         "Quitting...", vbCritical, "I Ain't Got No-Body!"
    Exit Function
End If

 ' Check to make sure the file exists...
If fso.FileExists(BodyFile$) = False Then
    MsgBox "The body file isn't where you say it is. " & vbNewLine & vbNewLine & _
           "Quitting...", vbCritical, "I Ain't Got No-Body!"
    Exit Function
End If

   ' Since we got a file, we can open it up.
    Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)

   ' and read it into a variable.
    MyBodyText = MyBody.ReadAll

   ' and close the file.
    MyBody.Close


   ' Now, we open Outlook for our own device..
    Set MyOutlook = New Outlook.Application


 ' Set up the database and query connections

    Set db = CurrentDb()

    Set MailList = db.OpenRecordset("qry_zzPJM_MyEmailAddresses")

 ' now, this is the meat and potatoes.
 ' this is where we loop through our list of addresses,
 ' adding them to e-mails and sending them.

Dim MyRecip As Outlook.Recipient

    Do Until MailList.EOF

        ' This creates the e-mail
        
        Set MyMail = MyOutlook.CreateItem(olMailItem)
            
            ' This addresses it
            'MyMail.To = MailList("email")
            Set MyRecip = MyMail.Recipients.Add("xxxxx@xxxxx.com")
            MyRecip.Type = olBCC
            Set MyRecip = MyMail.Recipients.Add(MailList("email"))
            
            'This gives it a subject
            MyMail.Subject = Subjectline$
            
            'This gives it the body
            ' This line will copy the "master" template into
            ' a variable we can mess around with

            MyNewBodyText = MyBodyText

            ' Now we can replace tokens to our heart's content
            ' without worrying about corrupting the "master" template

            'Customer first name for intro
            MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))
            'Summary of incident detail
            MyNewBodyText = Replace(MyNewBodyText, "[[Summary]]", MailList("Summary"))
            'Incident number for feedback
            MyNewBodyText = Replace(MyNewBodyText, "[[RNnumber]]", MailList("RNnumber"))
            'Date the call was raised
            MyNewBodyText = Replace(MyNewBodyText, "[[OpenDate]]", MailList("OpenDate"))
            
            MyMail.HTMLBody = MyNewBodyText

            'If you want to send an attachment
            'uncomment the following line and add the location of the file

            MyMail.Attachments.Add "C:\Users\masonpet\Documents\Customer Feedback for HP Support Incidents.xls", olByValue, 1, "Feedback"

            'This sends it!
            'MyMail.Send

            'Uncomment the next line And comment the "MyMail.Send" line above this.

            MyMail.Display


        
    'And on to the next one...
    MailList.MoveNext

I don't know VB, I got most of this from the internet and then tailored to work for me.
 
Last edited by a moderator:

mace¬

ClioSport Club Member
  Clio
I've managed to get the have replies sent to working....

'This adds the 'Have replies sent to'
MyMail.ReplyRecipients.Add ("email address")
 

mace¬

ClioSport Club Member
  Clio
All done.....

VBA confuses me. All this modern object orientated stuff. I'll stick with my legacy structured stuff.

MyMail.SentOnBehalfOf = (email)
 


Top