Feb 16th, 2016, 10:43 AM#2
Re: Excel VBA to compose an email in Outlook
I have used this routine successfully in many different codes. See if you can adapt it to your situation. This code will work even if Outlook is closed, however i do not need to enter a password to open Outlook.HTH
Code:Sub eml() Dim objOutlook As Object Dim objMail As Object Set objOutlook = CreateObject("Outlook.Application") Set objMail = objOutlook.CreateItem(0) With objMail .to = Range("B3").Value .cc = Range("C3").Value .bcc = Range("D3").Value .Subject = "Fund " & MonthName(Month(Date) - 1, False) & " " & Format(Date, "YYYY") .Body = Range("D3").Value '.Attachments.Add rngAttach.Value .Display 'Instead of .Display, you can use .Send to send the email _ or .Save to save a copy in the drafts folder End With Set objOutlook = Nothing Set objMail = Nothing 'Set rngTo = Nothing 'Set rngSubject = Nothing 'Set rngBody = Nothing 'Set rngAttach = Nothing End Sub
I'm a drinker with a coding problem...
All code is written with Excel 2010 - Please test all code on a backup copy of your data.
[Lesson 8] << [Contents] >> [Lesson 10]
In Lesson 8, we have understood the concept of data and learned how Visual Basic 2015 classifies various data types. Now, we shall learn how to store the data and how to declare them. Visual Basic 2015 data can be stored as variables or as constants. Variables are like mailboxes in the post office. The content of the variable changes every now and then, just like the mailboxes. In actual fact, variables are the specific areas allocated by the computer memory to store data.
9.1 Variable Names
Just like the mailboxes, each variable must be given a name. In order to name a variable in VB 2015, you need to follow a set of rules.The following are the rules when naming the variables in Visual Basic 2015:
- It must be less than 255 characters
- No spacing is allowed
- It must not begin with a number
- Period is not permitted
Some examples of valid and invalid variable names are displayed in Table 9.1
|Valid Names||Invalid Name|
|Long_Name_Can_beUSE||LongName&Canbe&Use *& is not acceptable|
9.2 Declaring Variables
In Visual Basic 2015, you need to declare the variables before using them. In order to declare a variable, you have to assign a name to the variable and state its data type. Variables are usually declared in the general section of the code windows using the Dim statement.
The syntax to declare a variable in Visual Basic 2015 is as follows:
Dim VariableName As DataType
If you want to declare more variables, you can declare them in separate lines or you may also combine more in one line, separating each variable with a comma, as follows:
Dim VariableName1 As DataType1, VariableName2 As DataType2, VariableName3 As DataType3
Example 9.1Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LoadDim password As String Dim MyName As String Dim Num1 As Integer Dim Num2 As Single Dim Sum As Integer Dim StartDate As Date End Sub
You may also combine the above statements in one line, separating each variable with a comma, as follows:
Dim password As String, MyName As String, Num1 As Integer, Num2 as Single. Sum as Integer, StartDate as Date
Example 9.2Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim YourName as String="George" Dim MyMsg As String
MyMsg = “Happy Birthday!” MsgBox(MyMsg&”,”&YourName)
Notice that you can assign a value to the string in one line using the = sign instead of declaring the variable and then give it a value in another line.Dim YourName as String="George"
is same asDim YourName as String YourName="George"
When you run the program, a message box that shows the text “Happy Birthday, George” will appear in a message box, as shown in Figure 9.1.
9.3 Assigning Values to Variables
After declaring various variables using the Dim statements, we can assign values to those variables. The syntax of an assignment in Visual Basic 2015 is
*You may also declare a variable by assigning an initial value to it, as shown in the following examples:Dim VarName as String="ABC" Dim VarNum as Interger=100
The variable can be a declared variable or a control property value. The expression could be a mathematical expression, a number, a string, a Boolean value (true or false) and more, as illustrated in the following examples:firstNumber=100 secondNumber=firstNumber-99 userName=”John Lyan” userpass.Text = password Label1.Visible = True Command1.Visible = false Label4.text = textbox1.Text ThirdNumber = Val(usernum1.Text) total = firstNumber + secondNumber+ThirdNumber MeanScore% = SumScores% / NumSubjects% X=sqr (16) TrimString= Ltrim (“ Visual Basic”, 4) Num=Int(Rnd*6)+1
An error will occur when you try to assign a value to a variable of the incompatible data type. For example, if you have declared a variable as an integer but you assigned a string value to it, an error will occur, as shown in Example 9.3:
Example 9.3Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim YourMessage As Integer YourMessage = "Happy Birthday!" MsgBox(YourMessage) End Sub
When you run the program, the following error messages will appear in a dialog box, as shown in Figure 9.2
9.4 Scope of Declaration
In Visual Basic 2015, we usually use the Dim keyword to declare the data. However, you can also use other keywords to declare the data. Three other keywords are private , static and public. The forms are as shown below:
Private VariableName as Datatype
Static VariableName as Datatype
Public VariableName as Datatype
The above keywords indicate the scope of the declaration. First of all, the keyword Private declares a local variable or a variable that is local to a procedure or module. However, the Private keyword is rarely used, we usually use the Dim keyword to declare a local variable. On the other hand, the Static keyword declares a variable that is being used multiple times, even after a procedure has been terminated.
Moreover, most variables created inside a procedure are discarded by Visual Basic when the procedure is terminated. However, the Static keyword preserves the value of a variable even after the procedure is terminated. Lastly, the Public keyword declares a global variable, which means it can be used by all the procedures and modules of the whole Visual Basic 2015 program.
9.5 Declaring Constants
Constants are different from variables in the sense that their values do not change during the running of the program. The syntax to declare a constant in Visual Basic 2015 is
Const Constant Name As Data Type = Value
Example 9.4Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Const Pi As Single = 3.142 Dim R As Single = 10 Dim AreaCircle As Single AreaCircle = Pi * R ^ 2 MsgBox("Area of circle with " & "radius" & R & "=" & AreaCircle) End Sub
Press F5 to run the program and clicking the button will produce the following message: