Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

Close Window (X)   
Excel VBA Course
[80% Discount] Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Today!)

Email Selected Cells In Body Vba

I am trying to insert a range of cell in the body of an outlook email with the same format. The code I am using now is below and it does insert anything in the email body.

Code:

Sub Mail()
Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = "cbelcher@jpfryelaw.com"
        .CC = ""
        .BCC = ""
        .Subject = "Burden Report"
        .Body = ActiveSheet.Range("A1:D12")
        .Attachments.Add ("C:\Documents and   Settings\cbelcher\Desktop\Burden Report.xls")
End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
    Set objMsg = Nothing
End Sub





Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

(80% Discount Ends Soon!)

View Course




Similar Topics







Below is the code I am using. I am trying to change the subject to show the value of a cell.

Code:

 
    With OutMail
        .To = "name@name.com"
        .CC = ""
        .BCC = ""
        .Subject = "Attrition for" & Range("c5")
        .Body = ""
        .Attachments.Add wb2.FullName


When it sends it does not show the name in c5.
It just reads "Attrition for"

Should there be a different code?


I can't find this solutions anywhere. I know how to send emails from Excel using VBA. When I get to the Body section, I want to insert a clickable link to a website, and also a clickable link to send an email (not as important as the website). Can this be done? I have found code to insert links to files, but none for website links.




In my Excel spreadsheet I have a column to store the email addresses of my clients. Every time I click on the text of an email address, it automatically opens Outlook with a new message composed to that address. How do I stop this from happening? I don't even use Outlook for my email and this is really frustrating. I've tried changing the format of the column to no avail.

Thank you; your help is most appreciated.


Hello All,

I need a macro enabled file to send HTMLbody email with pre populated text to list of receipents.

Any Idea How can i do?

Many thanks
Ben


Using VBA, I am trying to retrieve the contents of the Source of a web page (the same as would appear if you right-clicked on the page and chose "View Source") into a variable so I can work on it in VBA (using InStr, etc.).

The problem is I can use code such as
strHTMLText = ie.Document.body.innerText
or
strHTMLText = ie.Document.body.outerText
to retrieve the code, but in either case only part, not all, of the source code is captured. I need ALL the code. Is there some kind of code such as ie.Document.body.allText or similar that would perform this function?

Much obliged!


If I am using sendmail in a simple macro, is there any code I can add to actually open Outlook?

Some users of the macro are pressing the macro button and do not have Outlook open so the email isn't sent until they eventually open Outlook!

Thanks in advance


I have and excel sheet that was a contact list apparently exported wrong and has the names and addresses all together in each cell in about 350 rows in column A, they are all in this format

john doe email address here

for some reason the email address in brackets won't show up here but each email address is in brackets

I need to get the name in one column and the email address without the brackets into another, any ideas?


Hello,

As the title goes this is as simple as it gets. The macro worked good when I was using excel 2003 but now that I have 2007 everything works for except the resizing (Picture.Width & Picture. Height).

Code:

Sub AddImage()
Dim strPath As String
Dim strFile As String
Dim Picture As Object
Dim Response As String
Dim C As Range
strPath = "C:\\Photos\"
 
For Each C In Range("A1", Range("A65536").End(xlUp))
    If C  0 Then
        With C.Offset(0, 1)
            Set Picture = Nothing
            On Error Resume Next
            Set Picture = Sheets("Raw Pix").Pictures.Insert(strPath & C.Value & ".jpg")
            Picture.Top = .Top
            Picture.Left = .Left
            Picture.LockAspectRatio = msoTrue
            Picture.Width = 157
            Picture.Height = 138

        
    End With
    End If
     
NextC:
Next C
End Sub


I have tried to record some macros while I am resizing the pictures but to no avail the macros are blank.

Please help

Thanks!


I have a large database with names, addresses etc. When I try to make changes
to the email address, like change the font color or change on letter, Excel
tries to email the address. If I click in the formula bar to make the
changes, it automatically changes it back to all blue text and underlined. I
have tried to format all of the cells as "Text" to show as entered, but it
doesnt work. PLEASE HELP



Is there a trick to copy-paste a group of cells into Outlook? I don't want to paste as a picture (shows up as an attachment and is lost when someone else Replies), but formatting is skewed when pasted as an Excel object. Right now I'm recreating the table in Word, then pasting, which doesn't loose formatting.

A specific problem is cells which don't have borders show up with very light grey borders in Outlook. This, I do not want.

TiA


I'm trying to open a pdf file from within excel vba. I have tried using the followhyperlink method but adobe acrobat opens very briefly then immediately closes Code:

Sub OpenPDF()

'Dim pdf As String

    On Error Resume Next

    'pdf file to open
    pdf = "K:\PDF\mypdf.pdf"

    'open the pdf file
    ActiveWorkbook.FollowHyperlink pdf

End Sub


So then I tried to create an instance of acrobat by setting a reference to the acrobat object but I can't get this to work either!

The code I'm using is Code:

Sub OpenPDF()

    Dim pdf As AcroPDDoc
    Dim strPDF As String

    Set pdf = CreateObject("AcroExch.PDDoc")
    
    'pdf file to open
    strPDF = "K:\PDF\mypdf.pdf"

    'open the pdf file
    pdf.Open strPDF

End Sub


Any ideas what could be wrong with either approach?

Thank-you


I have a workbook that has compliance dates in columns "F","G" and "H" from row 7. What I need is when the date in either column comes within 30 days to auto send an email, address in column "A", recipients name in column "B". and then place todays date in column 'P". ALso need to send a follow up email when either date comes within 7days and then place todays date in column "Q". If there is a date in column "P" then don't send email. If there is a date in column "Q" then don't send follow up. Can this be done without the users intervention and each time the workbook is opened.
Thanks in advance for any assistance.

Mick


Hi
I am a new user and trying to setup a database in excel. I can select all addresses and copy but when trying to paste into excel it does so in 1 cell.

Is there a way I can paste the separate email id's into individual cells so I don't have to do it individually?

Many thanks
Patrick


I use a excel file through the course of the day and need to insert the current date in one column and the current time in the next column. I want to be able to just highlight the selected range of cells I need to insert into and hit a macro button and have the date and time inserted into just the cells I have highlighted. I'm not sure how to make this work with just the cells I've highlighted. Any help any one can give me would be greatly appreciated. Thanks!
Mike


Hi All,

I got the below macro which uses IE and open the URLs. I want to create a userform with listbox with radio button and commandbutton on the same which will help me to connect to each url when I select the same in listbox and click on the commandbutton.
Code:

Sub DoBrowse1()
    Dim ie As Object
    Set ie = CreateObject("Internetexplorer.Application")
    ie.Visible = True
    ie.Navigate "www.google.com"
End Sub


Any suggestions..


I'm trying to use VBA to go to a website that requires a User Name, Password, and a Submit Button.

So far I can get everything to work besides the Submit part. The code runs without errors, but doesn't actually "hit" the submit button on the webpage.

For posting, I removed my actual user name and password and and used the generic " User Name " and " Password " highlighted in blue.

I highlighted another section in green that I took from a previous post hoping it would solve my problem. The link is: http://www.mrexcel.com/forum/showthr...xplorer+submit


Here is the code I am using:

Sub GoToWebSiteAndPlayAroundNew()

Dim appIE As Object ' InternetExplorer.Application
Dim URL As String


Set appIE = CreateObject("InternetExplorer.Application")
URL = " https://efolio.morgankeegan.com/escripts/defaultLogon.asp?errCode=2 "


With appIE
.navigate URL
.Visible = True

Do While .busy: DoEvents: Loop
Do While .ReadyState 4: DoEvents: Loop

.document.getelementbyid("fUserName").Value = " UserName "
.document.getelementbyid("fPassword").Value = " Password "

End With


On Error Resume Next
x = 0
For Each mitem In IE.document.all
mitem.Value = "x"
x = x + 1
Next

x = 0
For Each mitem In IE.document.all
If x = "Submit" Then
mitem.Click
Exit For
End If

Next

End Sub


I'm using some basic code below in an on Workbook Open event to format cells with a value less then 2 and less than 1 with a particular color.

The code works, but it really slows my worksheet down when opening. Is there better way to write this? Thanks!

Code:

 
Dim myRange As Range
Dim cell As Range
Set myRange = Range("V6:V50000")
    For Each cell In myRange
    If cell.Value < 2 Then cell.Font.ColorIndex = 5
    If cell.Value < 1 Then cell.Font.ColorIndex = 3
    Next





Hi there, longtime user firsttime poster. Looking for some help as I am a non-expert with macros. Here's what I'm trying to do:

We have to submit things to a certain regulatory body and we usually enter tasks in as soon as they come, do the submission, and then keep a record of that submission.

So, I have a workbook with two sheets, one is "TO DO", the other is "ARCHIVE". Both sheets have the same columns and everything. I am looking for a macro that will automatically cut a (row) from the TO DO sheet and paste it in into the ARCHIVE sheet once it is done, then delete the cut row from the TO DO list so it stays topped up.

The trigger for archiving is the columns M and N which are titled "Complete ?" and each has a validation drop down that says "YES". When both cells in columns M and N have the YES in them, I would like the macro to make the above mentioned actions.

I ran a search on the forums and found something similar, but not quite what I was looking for.

Any help?


Hey guys,

I'm trying to write a macro which prints to PDF and saves the file name as the contents of a cell. I've been looking through all the posts currently on this forum to get something working. I'm using the following code -

Sub PrintPDF()
Filename = "C:\Documents and Settings\samb\My Documents\" & ActiveSheet.Range("Z1").Value
SendKeys Filename & "{ENTER}", False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF:", Collate:=True
End Sub

The macro prints to PDF, but then it stops at the Save As stage, where I have to manually enter the name of the file and click Save. I want the macro to automatically name the file with the contents of cell Z1. I then want it to then automatically press enter. Any ideas where I'm going wrong? Any help would be much appreciated!


Hello all,

Until now I have been able to find all my answers through searches. As a VBA novice, it has been very helpful. I am stumped on this one, however. I am trying to autofill from the selected cell in Column C down. I would like it to stop at the last cell with data in Column B. This is the code I have so far:

Range("C2").Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))

This does the autofill, but doesn't stop at the last cell with data in Column B.

In the past I have use this code to acheive similar results:

Dim endRow As Long
endRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C2").AutoFill Destination:=Range("C2:C" & endRow)

The problem with this code is that I will not always be starting in "C2". I need code that uses whatever the selected cell is.

All help is appreciated. Thanks!