473,387 Members | 1,897 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

VBA InputBox Automatic Population

1
I have a function which produces an email for a specific contact, and I have a report which again is for the same specific contact. This function opens Outlook and enters the email data into the message body (derived from one Query), and it then attaches the report (derived from another query) to the email.

This routine works perfectly, however, it means I have to enter the Contact name into both InputBoxes. Is there a way to use the input from the first InputBox to populate the second?

Here is my code:
Option Compare Database
Option Explicit

Public Function SendEMail()
'define the variables
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 Contact As String
Dim qryMail As QueryDef


'offer for user inut
Contact$ = InputBox$("Contact?")
'Use the query to select data for chosen Name
Set qryMail = CurrentDb.QueryDefs("Query1")
qryMail.Parameters(0) = Contact$
'Call Outlook
Set MailList = qryMail.OpenRecordset
Set fso = New FileSystemObject
Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
'Collate the email basic information
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.To = MailList("Email1")
MyMail.Subject = MailList("Subject")
MyMail.Body = MailList("OpeningSalutation") & vbNewLine & vbNewLine & MailList("Paragraph1") & vbNewLine & vbNewLine & MailList("Paragraph2") & vbNewLine & vbNewLine & MailList("Paragraph3") & vbNewLine & vbNewLine & MailList("Paragraph4") & vbNewLine & vbNewLine & MailList("ClosingSalutation") & vbNewLine & vbNewLine & MailList("Name")
'Export the attachment
DoCmd.RunSavedImportExport ("Export-FORM")

MsgBox Contact$, vbDefaultButton1

'Import the attachment onto email
MyMail.Attachments.Add "C:\Users\Hazel\Documents\FORM.rtf", olByValue, 1, "My Displayname"
MyMail.Display

End Function
Apr 26 '10 #1
0 1059

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: news.microsoft.com | last post by:
hey all, I am having an odd problem with the InputBox in VB.NET, and it ONLY happens on one of my computers. When I try "Dim x as String = InputBox("TESTPROMPT"), I get the following error: ...
0
by: Megan | last post by:
Data Comparison: Sample Versus Rest of Population. I have a population of data, and I want to take a sample of that population and compare it against the entire population. I have a database...
2
by: missimaths | last post by:
I was wondering if anyone knew of a way to control the text a user types into an inputbox? I want the user to type in four letters only. I can check the lenght by using the len() function in vba...
1
by: Hans Kamp | last post by:
How do I use InputBox? private void addButton_Click(object sender, System.EventArgs e) { string newName; newName = InputBox("t1", "t2", "t3"); namesListBox.Items.Add(newName); }
1
by: | last post by:
When trying to use the InputBox function I get the error "InputBox is a namespace and therefore is not a valid expression". Can anyone tell me what the problem is? Thanks.
8
by: jpoquette | last post by:
I have recently upgraded a Visual Basic 2003 win forms application to 2005. After doing so I can no longer get my project to compile. The code is bombing on any line that uses an InputBox. I'm...
0
by: robert | last post by:
I'm looking for an efficient optimizer on a noisy high-dimensional and costly function. My own GA hack seems to be somewhat stiff and I find me trying too much around with different cooling speeds...
14
by: neonman14 | last post by:
Hello I am in Intro to Java Programming and I am having problems with assignment. The Homework assignment is called Population. Population Write a program that will predict the size of a...
1
by: Clive Swan | last post by:
Hi I am trying to sum a population field that may have 140 records for each Ward (example). Any suggestions on the best way to do this. I would like to have a unique record for each Ward...
1
by: tg | last post by:
http://img522.imageshack.us/img522/8647/scan10005ci7.jpg As a percentage of world inhabitants, the white population will plummet to a single digit (9.76%) by 2060 from a high-water mark of...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.