vb6 access200- data to a email template

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

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.

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

Thanks
Joe

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.

Quote:

---

Originally Posted by joefox

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.

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 ;) )

VB Code:

'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

VB Code:

'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:

VB Code:

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

Hello, i would be using using Microsoft Outlook 2002.

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!

VB Code:

'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)

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

VB Code:

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)

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 :)

VB Code:

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

assuming you have a database field called email:

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

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?

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

Thanks again!
Joe
:wave: :) :confused: :D :bigyello: :sick: :ehh: :eek: :thumb: ;) :blush:

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

Same again really:

VB Code:

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

GOT IT!
Perfect! Thanks Everyone!
:lol: ;) :eek2: :wave: :thumb: :D :) :blush: :p :bigyello: :eek: :mike: :thumb: :wave: :cool: