vb6 access200- data to a email template

[joefox]

Hello everyone, thank you for all your help on my project i am trying to do.

I'm not sure if this is possiable or not, but.
I wanted to take a customers information that i pulled up in my database, throught a gui interface made in vb. And i wanted to make a "email" button, so that it would take certain values from the fields that i pulled up, and insert them into places in the email document.

Such as, when i hit the "email" button, i have a template saved on my server or somewhere, and it will take the first name and last name, and insert them into the outlook message template so that i dont have to type them in there?

Is something like this even possiable?

Thanks again
Joe

[PilgrimPete]

Sure it's possible.
There are a few limitations if you want it to be a simple process, but yes, in principle it is possible.

It sounds like the easiest way would be to reference the Outlook object library in your project and create a message from there - either hard-coding the content or storing it in a text/xml/db file that you read in and 'fill out' each time.
[This restricts you if you ever want to distribute your application, as it relies on the Outlook object model, but creating SMTP clients etc. in VB6 involves a nasty trip into Windows APIs...]

Hope this starts you off.

[joefox]

Yeah, Im looking for the easiest way to do it.
how would I even start a button like this?
Just curious.

Thanks
Joe

[dglienna]

keep in mind that outlook throws up a warning for each record that you want to email, and won't let you approve it for 5 seconds. i automated a mailmerge, and couldn't get around it. I recommend the vbsendmail approach. Serch for it.
If I didn't have to do everything that the MailMerge did, I would have, also, but smtp doesn't work with MM.

[RobDog888]

Yeah, Im looking for the easiest way to do it.
how would I even start a button like this?
Just curious.

Thanks
Joe

like previously posted, we need to know what you want to use to do the emailing (Outlook vbSendMail, SMTP,etc.).
If the Outlook Security Popup warning message is not a problem, then Outlook will be the easiest.

[si_the_geek]

Actually it sounds to me like a formatted "mailto" would be appropriate in this situation, it depends if the email should come up on screen & be editable (the "send" must be done manually if so).

If that is what you want, this code will work nicely: (otherwise, RobDog888 is the man in the know )

'This code can be pasted as-is into a form (only usable in that form), 
            'or a module (can be used anywhere in the project)
'in "general declarations":
Private 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
Const SW_SHOWNORMAL = 1
 
'the sub:
Sub My_Mailto(pr_Recipients_s As String, _
              Optional pr_Subject_s As String = "", _
              Optional pr_Body_s As String = "", _
              Optional pr_CC_s As String = "", _
              Optional pr_BCC_s As String = "")
'---------------------------------------------------------------------------------------
' Purpose   :  Create an email (not sent automatically)
' Returns   :  (none)
' Parameters:  (various email fields)
' Author    :  Si_the_geek (vbForums.com)
' Date      :  Fri 14 Jan 2005
'---------------------------------------------------------------------------------------
 
Dim vl_Execute_s As String      'Format the parameters
  vl_Execute_s = "mailto:" & pr_Recipients_s
 
  If (pr_Subject_s <> "") Then
    vl_Execute_s = vl_Execute_s & "&subject=" & pr_Subject_s
  End If
  If (pr_CC_s <> "") Then
    vl_Execute_s = vl_Execute_s & "&cc=" & pr_CC_s
  End If
  If (pr_BCC_s <> "") Then
    vl_Execute_s = vl_Execute_s & "&bcc=" & pr_BCC_s
  End If
  If (pr_Body_s <> "") Then
    vl_Execute_s = vl_Execute_s & "&body=" & pr_Body_s
  End If
 
                                'Convert CR's to HTML CR's
    '(note - this works well for Outlook, not tested on other clients)
  vl_Execute_s = Replace(vl_Execute_s, vbCr, "%0A")
 
                                'Create the mail
  ShellExecute 0, vbNullString, vl_Execute_s, vbNullString, "C:\", SW_SHOWNORMAL
 
End Sub

'example usage:
Dim sBodyText as String
sBodyText = "body text here" & vbcr & "testing testing"
Call My_Mailto ("[email protected]","subject here",sBodyText)

In this particular case you can load the "template" into the sBodyText variable, and put the specific name data in before the call to the sub.

I would personally store the templates in the database, and use markers in the templates (such as {#1#}) so that you can replace them easily in your code, like this:

sBodyText = "body text here" & vbcr & "testing {#1#} testing {#2#}*"
 
sBodyText = replace(sBodyText, "*#1#*", txtFirstName.text)
sBodyText = replace(sBodyText, "*#1#*", txtSurname.text)
Call My_Mailto (....

[joefox]

Hello, i would be using using Microsoft Outlook 2002.

[joefox]

Hello,
Thank you for the code, But i dont see where it put in the values that are displayed in my visual basic program.
I wanted to say search from someone in my database, it pulls up there info, then i hit the "email" button, and it basiclly, takes the fname lname serialnumber fields, and puts them into the email template, that way all i have to do is look it over, and hit send.. here is what i have so far with the code..

Thanks for the help everyone!

'the sub:
Sub My_Mailto(pr_Recipients_s As String, _
              Optional pr_Subject_s As String = "", _
              Optional pr_Body_s As String = "", _
              Optional pr_CC_s As String = "", _
              Optional pr_BCC_s As String = "")
 
Dim vl_Execute_s As String
  vl_Execute_s = "mailto:" & pr_Recipients_s
 
  If (pr_Subject_s <> "") Then
    vl_Execute_s = vl_Execute_s & "&subject=" & pr_Subject_s
  End If
  If (pr_CC_s <> "") Then
    vl_Execute_s = vl_Execute_s & "&cc=" & pr_CC_s
  End If
  If (pr_BCC_s <> "") Then
    vl_Execute_s = vl_Execute_s & "&bcc=" & pr_BCC_s
  End If
  If (pr_Body_s <> "") Then
    vl_Execute_s = vl_Execute_s & "&body=" & pr_Body_s
  End If
 
vl_Execute_s = Replace(vl_Execute_s, vbCr, "%0A")
'Create the mail
ShellExecute 0, vbNullString, vl_Execute_s, vbNullString, "C:\", SW_SHOWNORMAL
 
' Example Usage
Dim sBodyText As String
sBodyText = "Hello," & vbCr & "Thank you for your time and patience.." & vbCr & "Your new serial number is: "
Call My_Mailto("[email protected]", "New Serial Number", sBodyText)
 
sBodyText = "body text here" & vbCr & "testing {#1#} testing {#2#}*"
 
sBodyText = Replace(sBodyText, "*#1#*", txtFirstName.Text)
sBodyText = Replace(sBodyText, "*#1#*", txtSurname.Text)

[si_the_geek]

Once you have the "My_MailTo" sub in your project, you just need something like this:

Dim sBodyText As String
sBodyText = "Hello " & rs("fname") & " " & rs("lname") & "," & vbCr _
          & "Thank you for your time and patience.." & vbCr _
          & "Your new serial number is: " & rs("serialnumber")
Call My_Mailto("[email protected]", "New Serial Number", sBodyText)

I was assuming before that you wanted multiple email templates (hence the Replace business)

[joefox]

Hello SI..
Dang you guys are good..

Ok it worked perfectly..
My only question is...
I needed the mailto, to be the customer email address, not my email.
Same

here is what i have

Private Sub EmailtheCustomer_Click()
Dim sBodyText As String
sBodyText = "Hello " & rs("fname") & " " & rs("lname") & "," & vbCr _
          & "Thank you for your time and patience.." & vbCr _
          & "Your new serial number is: " & rs("serialnumbersoftware")
Call My_Mailto("[email protected]", "New Serial Number", sBodyText)
 
End Sub

[si_the_geek]

assuming you have a database field called email:

Call My_Mailto(rs("email"), "New Serial Number", sBodyText)

[joefox]

wow..again works like a charm..

Now if i wanted to make another button, that does the same function, but is a different template, i would just make a different button, and change the values in that button right?

[joefox]

Ah i figured it out, i just made a new button, and changed the template in it.

Thanks again!
Joe

[joefox]

Oh wait, just another quick question, what if i wanted to have text in my subject and a value from my database, such as:

Subject: New Serial Number for (value in database)

Thanks
Joe

[PilgrimPete]

Same again really:

Dim strSubject as string
strSubject = "New Serial Number for " & rs("value in database")
Call My_Mailto(rs("email"), strSubject , sBodyText)

[joefox]

GOT IT!
Perfect! Thanks Everyone!
:mike: